I link to the sql server table through a function with vba code. This function is called in the AutoExec Macro. This way it is not necessary to setup odbc on the clients PC.
Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim sConnString As String
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=yourpassword;"
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function
Nice code, but does that mean I have to set up this piece of code
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
for every table in the database to relink (we have 32 tables)
Probably not if all the linked tables are from 1 source. I put the table name in because I had only 1 linked table. If all the tables are from 1 source then just check for type=LINK. Otherwise, maybe there is a way to tell from one of the Jet properties - never tried.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.