asked on

Use DSN-less Connection to Link MS SQL Views Without UID Warning

Hi, Experts!

I have an MS Access 2016 application that uses a connection string in code to re-link all SQL tables when the application is opened. It works fine with tables, however if I link a view the user always gets the "Select UID" window for each view. Is there a way around this?

Here's my code. There is a local table called "tblAppTables" that has the SQL name and local name of all the tables/views.

Public Function RefreshAppTables() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConnection As String

strConnection = "DRIVER=SQL Server;SERVER=192.168.0.25;DATABASE=MyDB;UID=MyUse;PWD=MYPassword"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAppTables")

DoCmd.SetWarnings False
DoCmd.Hourglass True

rs.MoveFirst
While Not rs.EOF
    On Error Resume Next
        DoCmd.DeleteObject acTable, rs("LocalName")
    On Error GoTo 0
    DoCmd.TransferDatabase acLink, "ODBC", "ODBC;" & strConnection, acTable, rs("SQLName"), rs("LocalName"), False, True
    rs.MoveNext
Wend

MsgBox "All Tables Refreshed"

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Function

Open in new window

Thanks!

Kelvin Sparks

When Access links to an object (table or View) is must know the primary key (or unique index) to use with this link (or that link becomes read only).
What you need to do is to trap the error when it happens and then, based on the name of it (the view) create a unique index in access for it before moving onto the next view). Easy is there's only a few views, but could a real issue if many.

If you only need read only link, then you can trap and ignore that message)

Kelvin

Christopher Deen

Microsoft provides drivers on Windows for non-SQL database systems such ... 3but there are close links between unixODBC and Easysoft, and ... "DSN=dsn;UID=uid;PWD=pwd" but it can be constructed without a DSN by specifying a driver ... To find out what tables are accessible from a connection ch, use.
https://www.justcerts.com/

Member_2_241474

ASKER

There is no error to trap that I can trap in VBA. I am connecting to MS SQL 2102, and every time I link a view, manually or through code, I get a window that says, "Select Unique Identifier." A view of course does not have a UID/key and I have not been able to find how to give it one.

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_241474

ASKER

That works! Thanks

James Spence

ExamCertify gives in a flash downloadable exam planning materials for multi-seller exams, including Cisco, CompTIA, Microsoft, Oracle, HP, IBM and that's just the beginning.

http://www.examcertify.com/