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")
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
MsgBox "All Tables Refreshed"