Popup for Primary Key

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

Answer 1

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

Answer 2

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 [e-mail].

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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