Classroom at a Private School |
They were users of Access, and thus wanted the data in Access format. So we had to create for them a linked Access database. I assume that they would pass the file around, and would be frustrated to learn they had to create a file or user DSN connection to the data.
So I created a file DSN, created the linked tables, then ran this code.
Sub main()
strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=Servername;" & _
"Database=databaseName;" & _
"Trusted_Connection=Yes"
Dim t As Integer
Dim db as Database
Set db = CurrentDb
For t = 0 To db .TableDefs.Count - 1
If Len(db .TableDefs(t).SourceTableName) > 0 Then
db .TableDefs(t).Connect = strConnectionString
db.TableDefs(t).RefreshLink
End If
Next
MsgBox("All Done!")
End Sub
Run this VBA macro once, and the connection strings are now embedded in the table definitions. Easy-Cheesy.