none
Popup for Primary Key RRS feed

  • Question

  • Hi... i have a problem when during linking Views as Tables from SQL Server to Access front-end using Docmd.TransferDatabase, system pops up the box where it asks for selecting the Primary Key. I don't want to select primary keys for Views and want to complete whole process quietly without any pop-up.

    I've tried to use SendKeys to automatically press Enter key when it detects View based on its naming convention, but for the first time pop-up appears anyhow and I have to press Enter key. After this first press, Sendkeys does its work.

    Could u guys please advice the best solution to deal with this?

    Regards,
    K
    Saturday, January 14, 2017 3:38 AM

Answers

  • I would suggest you create the table link in VBA code. If you use code to create the link (as opposed to transferDatabase), then you are not prompted for the PK ID.

    This code snip will do the trick:

      
       Dim strCon        As String
       strCon = CurrentDb.QueryDefs("qrypassR").Connect
       
       Dim newLink       As DAO.tableDef
       
       Set newLink = CurrentDb.CreateTableDef("VHotels")     ' local link name
       
       newLink.Connect = strCon
       newLink.SourceTableName = "VHotels"
       
       CurrentDb.TableDefs.Append newLink
       Application.RefreshDatabaseWindow            ' this is optional

    Note that the above will result in a linked table – but it will be read only since we did not set the PK. It does NOT prompt you for the PK.

    And I just “robbed” and lifted the connection string from a known existing linked table. If you only need to pull data from that view, then the above should work just fine – and it will not prompt you.

    If you need the View to be updatable, then you have to add additional code to set the PK. Add this line to the end of above, and the linked view will be updatable if you need:

    CurrentDb.Execute "CREATE UNIQUE INDEX IXPK ON VHotels (ID) WITH PRIMARY"

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Thursday, January 19, 2017 1:15 AM

All replies

  • Then assign a unique key for the view. Having the key, Access will not ask for it.
    Like:

    CREATE VIEW v_test WITH SCHEMABINDING 
    AS
        SELECT id from table
    GO
    CREATE UNIQUE CLUSTERED INDEX idx_id ON v_test (id)
    GO


    • Edited by SysGreg Saturday, January 14, 2017 10:45 PM
    Saturday, January 14, 2017 10:45 PM
  • Hi KhurramKZ,

    you had mentioned that," I don't want to select primary keys for Views."

    generally , it's not recommended.

    instead of that you add the index in your code as it is already mentioned by other community member.

    here is the another example of that.

    CREATE VIEW v_abc WITH SCHEMABINDING AS
    SELECT a, b, c
    FROM dbo.wide_tbl
    WHERE a BETWEEN 0 AND 1000
    CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)
    

    Reference:

    Designing Indexed Views

    please visit the thread below , in which other community member had the similar issue like yours in the past. may help you.

    Suppress Linked View Unique Record Popup

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 16, 2017 1:51 AM
    Moderator
  • Thank you, I tried to do so but seems like now i've another problem. I have tried using derived tables to generate a unique value for Unique Index but Derived Tables are not acceptable for Schema-bounded Unique Indexes. I've tried to use NEWID() and generated a unique ID for every field, but it doesn't work either telling me that only deterministic values are allowed.
    There's no other  unique key in most of my views. Now what to do please?

    Pasting code below, if it helps please.

    Regards,
    K

    IF OBJECT_ID('vuDailySE_Progress_GroupBy', 'V') IS NOT NULL
        DROP VIEW vuDailySE_Progress_GroupBy
    GO
     
    CREATE VIEW vuDailySE_Progress_GroupBy
        WITH SCHEMABINDING
    AS
    SELECT
        ROW_NUMBER() OVER (ORDER BY DateAdded) AS ID, DateAdded, Region, Location, Project, PM, SupervisorID, AreaID, WorkPackID, [Earned HoursSum], [Earned ValueSum]
    FROM (
        SELECT
            --NEWID() AS ViewID,
            CONVERT(Date, tblProgress_Daily.Date_Added) AS DateAdded,
            tblWBS_Lev0_Project.Region,
            tblWBS_Lev0_Project.Location,
            tblWBS_Lev0_Project.ID                        AS Project,
            tblEmployee.FullName                        AS PM,
            tblProgress_Daily.SupervisorID,
            tblWBS_Lev2_Area.ID                            AS AreaID,
            tblWBS_Lev3_WorkPack.ID                        AS WorkPackID,
            Sum(tblProgress_Daily.Today_ManHour)        AS [Earned HoursSum],
            Sum(tblProgress_Daily.Today_Earned_Value)    AS [Earned ValueSum],
            COUNT_BIG(*)                                AS RecCount    
        FROM            
            dbo.tblWBS_Lev0_Project
                INNER JOIN
            dbo.tblROC_InstallationType ON tblWBS_Lev0_Project.ID = tblROC_InstallationType.ProjectID
                INNER JOIN
            dbo.tblWBS_Lev3_WorkPack    ON tblROC_InstallationType.WorkPackID = tblWBS_Lev3_WorkPack.ID
                INNER JOIN
            dbo.tblWBS_Lev2_Area        ON tblROC_InstallationType.AreaID = tblWBS_Lev2_Area.ID
                INNER JOIN
            dbo.tblBoQ                    ON tblROC_InstallationType.ID = tblBoQ.InstallationTypeID
                INNER JOIN
            dbo.tblBoQ_Progress            ON tblBoQ.ID = tblBoQ_Progress.BoQID
                INNER JOIN
            dbo.tblProgress_Daily        ON tblBoQ_Progress.ID = tblProgress_Daily.BoQProgressID
                INNER JOIN
            dbo.tblProject_Supervisor    ON tblProgress_Daily.SupervisorID = tblProject_Supervisor.ID
                INNER JOIN
            dbo.tblProjectManager        ON tblProject_Supervisor.ProjectManagerID = tblProjectManager.ID
                INNER JOIN
            dbo.tblEmployee                ON tblProjectManager.ProjectManager = tblEmployee.ID
        GROUP BY
            CONVERT(Date, [tblProgress_Daily].[Date_Added]),
            tblWBS_Lev0_Project.Region,
            tblWBS_Lev0_Project.Location,
            tblWBS_Lev0_Project.ID,
            tblEmployee.FullName,
            tblProgress_Daily.SupervisorID,
            tblWBS_Lev2_Area.ID,
            tblWBS_Lev3_WorkPack.ID
    ) AS MyResults
    GO

    CREATE UNIQUE CLUSTERED INDEX IX_U_SEProgressGroupby ON vuDailySE_Progress_GroupBy(ViewID)
    GO

    Monday, January 16, 2017 5:51 AM
  • If you can't find a field with unique key you are unable to create the index. sorry. from my experience by altering the view you can in most cases find a unique value of a PK from some table. take a nother look at the views and maybe add an field from existing tables that is unique to that view.
    Monday, January 16, 2017 2:24 PM
  • I would suggest you create the table link in VBA code. If you use code to create the link (as opposed to transferDatabase), then you are not prompted for the PK ID.

    This code snip will do the trick:

      
       Dim strCon        As String
       strCon = CurrentDb.QueryDefs("qrypassR").Connect
       
       Dim newLink       As DAO.tableDef
       
       Set newLink = CurrentDb.CreateTableDef("VHotels")     ' local link name
       
       newLink.Connect = strCon
       newLink.SourceTableName = "VHotels"
       
       CurrentDb.TableDefs.Append newLink
       Application.RefreshDatabaseWindow            ' this is optional

    Note that the above will result in a linked table – but it will be read only since we did not set the PK. It does NOT prompt you for the PK.

    And I just “robbed” and lifted the connection string from a known existing linked table. If you only need to pull data from that view, then the above should work just fine – and it will not prompt you.

    If you need the View to be updatable, then you have to add additional code to set the PK. Add this line to the end of above, and the linked view will be updatable if you need:

    CurrentDb.Execute "CREATE UNIQUE INDEX IXPK ON VHotels (ID) WITH PRIMARY"

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Thursday, January 19, 2017 1:15 AM
  • There is no benefit in attempting to create a PK that already exists on the base table in a SQL view - it generally not required (in fact not sure it even allowed). And since it is the full table, then SQL will ALWAYS use the base table indexing for views anyway when the query plan is built.

    The problem is when you create a link to a SQL view via transferDatabase, it WILL prompt you for a PK. When you link to a SQL table, transferDatabase does not.

    The workaround and code snip I posted above works fine when creating a link to SQL server views in VBA. I unfortunately don't know if it possible to pluck out the PK key from the underlying table when linking to a view - so my example does hard code and assume the PK is known beforehand.

    However, if the poster don't need a updateable view - then the additional code to set the PK is not required. The crux here is the quirk of the TransferDatabase command in Access - it always prompts for the PK when linking to a view.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, January 19, 2017 1:47 AM
  • Thank you sooooooo much. I was feeling there should be something. Yes I need read only for now.

    Actually the problem was that views are a result of Group By and it was getting impossible for me to get one deterministic ID key without Derived Tables which Schema bound view doesn't accept.

    Thanks once again, you solved me a big problem :)

    Regards,

    K

    Thursday, January 19, 2017 11:04 AM