' basically, the linked tables are set with values.
' My process has a Local Table with all of the TableName
' A procedure (not shown) looks at each table name and sets up the
' DNSLess2 settings
' Then at the end of the connection - it refreshes the collection
' you will probably be interested in the refresh below for your error
Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
Dim ConnectString As String
Dim strConnectionString As String
Dim scn As String
Dim sLocalName As String
Dim UID As String
Dim pwd As String
Dim ServerString As String
ServerString = "MyServerInstance\MyDBInstance"
UID = "MySuperUserID" ' temp SQL name change later ' example UN/PW SQL security
pwd = "MyPW"
sLocalName = TableName ' "ATableName" ' <<<---manually add a table name here for one single table
Dim DatabaseName As String
If fTest = False Then
DatabaseName = "MYDBName"
Else
DatabaseName = "MYTESTDBName" ' <<<<<<<<<<------------ DBT or DB ////////Uncomment for Test DB Server name next
End If
' example of hard coded Server / SQL Server name - instead of variable
strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
"SERVER=MyServerl\MySQLServer;DATABASE=" & DatabaseName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & pwd & ";" & _
"Table=DBO." & sLocalName & ";Option=3;"
strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
"SERVER=MyServerl\MySQLServer;DATABASE=" & DatabaseName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & pwd & ";" & _
"Table=DBO." & sLocalName & ";Option=3;"
ModifiedRefreshDNSLess2 = strConnectionString
'Debug.Print strConnectionString
End Function
Sub RerefreshLinkedTables() ' refreshes Linked tables doesn't refresh icon
' On your error, this might be the refresh you are looking for
Dim dbs As DAO.Database
Dim tdf As TableDef
10 Set dbs = CurrentDb
20 For Each tdf In dbs.TableDefs
30 If Len(tdf.Connect) > 0 Then
40 tdf.RefreshLink
'Debug.Print tdf.Name
50 End If
60 Next tdf
70 Set dbs = Nothing
End Sub
