Repoint Access linked tables to different connection string

I’ve been looking for a way to do this for a project I’m working on.

The access app has linked tables that are on a SQL server, but it doesn’t use a DSN.  Instead the connection string is somewhere unknown to the user and so repointing it (like user server1 instead of server2, or db1 instead of db2) was a major pain point.

Finally I found some code that I can use to change it.  I changed some names, but you get the idea. 

Private Sub Detail_DblClick(ByVal Cancel As Integer)
     Dim db As Database, source As String, path As String
     Dim dbsource As String, i As Integer, j As Integer

     db = DBEngine.Workspaces(0).Databases(0)

     For i = 0 To db.TableDefs.count - 1
         If db.TableDefs(i).Connect <> " " Then
             If Right(db.TableDefs(i).Connect, 15) = "DbNameHere12345" Then
                 db.TableDefs(i).Connect = db.TableDefs(i).Connect & "Updated"
                 db.TableDefs(i).RefreshLink()
             End If

         End If
     Next
 End Sub

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s