Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-10-25
6
Medium Priority
?
99 Views
Last Modified: 2016-11-24
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!
0
Comment
Question by:jriggin
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41859663
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
0
 

Expert Comment

by:Christopher Deen
ID: 41859868
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/
0
 
LVL 1

Author Comment

by:jriggin
ID: 41860323
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 2000 total points
ID: 41860742
I use similar code, but not the DoCmd.TransferDatabase

Try this alternative

Do Until rs.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rs!LocalName Then
            ''Build the dsn-less connection string
            db.TableDefs.Delete rs!LocalName
            Exit For
        End If
    Next
        strConnect = "ODBC;Driver={SQL Server};Server=192.168.0.25;DatabaseMyDB;UID=MyUse;PWD=MYPassword"
        Debug.Print strConnect
        Set tdf = db.CreateTableDef(rsLocalName, dbAttachSavePWD, rs!SQLName, strConnect)
        db.TableDefs.Append tdf
    rs.MoveNext
Loop



Kelvin
1
 
LVL 1

Author Closing Comment

by:jriggin
ID: 41861022
That works!  Thanks
0
 

Expert Comment

by:James Spence
ID: 41901174
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/
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question