Primary Key popup (1 Viewer)

[khurram7x]

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

 

[Ranman256]

You don't have to.
It has no effect when I Did it.

But do it when connecting the table and it won't ask again.
Which means you have to redo the ODBC link, but just the once.

 

[The_Doc_Man]

I'm with RanMan. This is, or is derived from, an error in how you used the Linked Table Manager.

Just as a fine point, I always use a query to drive forms & reports even if it is a single-table document. Though I could never reproduce it at will, there were times when I got asked for the location of the table. If you swap out the table for a query that has every field from the same table, it never asks about table location.

 

[khurram7x]

Thanks for the advice DocMan about table>query, will keep in mind.
I'm using below script to import and rename tables. I understand if i select OK on the popup first time, it does not appear back but whole process need to execute silently so that management has one copy of Access that they could use to connect remotely with several projects/databases at the click of button. I don't want to show them the View structure.

Let me know please if there's any better way to achieve this?
If it could be done, great, and if not, then they should be keeping a different copy for each project.

Thank you,
K

Dim strConnect As String
Dim RecSet As Recordset
Dim PauseTime, Start

'**RunPassThroughSelect is a Pass-Through query function, returning name of tables in SQL Server database
RunPassThroughSELECT ("SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES")

strConnect = "DSN=****;UID=****;PWD=****;DATABASE****;Network=DBNMPNTW"

Set RecSet = CurrentDb.OpenRecordset("qrySQLPass")

Do Until RecSet.EOF
Debug.Print RecSet!table_name

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;" & strConnect, , RecSet!table_name, RecSet!table_name, , True

'**Below in single quotes are the things I tried. Nothing helped perfect
' If Left(RecSet!table_name, 2) = "vu" Then
' 'CurrentDb.Execute "CREATE INDEX IX_PK_ID ON '" & RecSet!table_name & "' (ID) WITH PRIMARY"
' PauseTime = 1 ' Set duration.
' Start = Timer ' Set start time.
' Do While Timer < Start + PauseTime
' DoEvents ' Yield to other processes.
' Loop
' SendKeys "{ENTER}"
' End If

RecSet.MoveNext
Loop

RecSet.Close
Set RecSet = Nothing

 

[khurram7x]

I have tried using derived tables to generate a unique value for Schema-bounded 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 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
.
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

 

[The_Doc_Man]

I'll have to bow out of this one because you are dealing with SQL server and I have no experience with this one. However, other members do have such experience and might be able to guide you better.

 

[khurram7x]

Sorry DocMan, just realized it's an SQL Server issue at this point. I'll have another look and see if i could find and add a unique key from the table into the view. Thank you.

 

[Minty]

You should have a look here http://stackoverflow.com/questions/137803/sql-server-select-statement-with-auto-generate-row-id lots of suggestions for an ID in a select statement