Answered by:
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,
KSaturday, 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- Edited by Albert D. Kallal Thursday, January 19, 2017 1:22 AM
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Thursday, January 19, 2017 3:02 AM
- Marked as answer by KhurramKZ Thursday, January 19, 2017 11:08 AM
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:
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
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 -
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,
KIF 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)
GOMonday, 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.
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Tuesday, January 17, 2017 5:21 AM
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- Edited by Albert D. Kallal Thursday, January 19, 2017 1:22 AM
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Thursday, January 19, 2017 3:02 AM
- Marked as answer by KhurramKZ Thursday, January 19, 2017 11:08 AM
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