DSN-less Access Linked table.

Classroom at a Private School

It was an emergency!  We had to restore an SQL Server backup for a group as quickly as possible.  So we did.  Except, of course, they didn't now how to access said data.

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.