Reconnect linked tables on Start-up

        Not Rated      6,304 views      1 comments
by Allen Browne, 20 April 2005    (Access 2+)

MS-Access Tips for Serious Users

Provided by Peter Vukovic


Reconnect Attached tables on Start-up

If you have an Access application split into DATA.MDB and PRG.MDB (see Split your MDB file into data and application ), and move the files to a different directory, all tables need to be reconnected. Peter Vukovic's function handles the reconnection.

If your data and program are in different folders, see Dev Ashish's solution: Relink Access tables from code

Function Reconnect ()
'**************************************************************
'*     START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'*     AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'*     WHEN THE 'DATA.MDB'  AND THE 'PRG.MDB'
'*     ARE IN THE SAME DIRECTORY!!!
'*                  PROGRAMMING BY PETER VUKOVIC, Germany
'*                  100700.1262@compuserve.com
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = dbengine.Workspaces(0).Databases(0)
'*************************************************************
'*                     RECOGNIZE THE PATH                    *
'*************************************************************

For i = Len(db.name) To 1 Step -1
    If Mid(db.name, i, 1) = Chr(92) Then
        path = Mid(db.name, 1, i)
        'MsgBox (path)
        Exit For
    End If
Next
'*************************************************************
'*              CHANGE THE PATH   AND   CONNECT  AGAIN       *
'*************************************************************

For i = 0 To db.tabledefs.count - 1
    If db.tabledefs(i).connect <> " " Then
        source = Mid(db.tabledefs(i).connect, 11)
        'Debug.Print source
        For j = Len(source) To 1 Step -1
            If Mid(source, j, 1) = Chr(92) Then
               dbsource = Mid(source, j + 1, Len(source))
               source = Mid(source, 1, j)
                   If source <> path Then
                        db.tabledefs(i).connect = ";Database=" + path + dbsource
                        db.tabledefs(i).RefreshLink
                        'Debug.Print ";Database=" + path + dbsource
                    End If
                Exit For
            End If
         Next
    End If
Next
End Function

Provided by Peter Vukovic, September 1995.

Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Reconnect linked tables on Start-up'?


1.

John Smith says...

07 May 2009

 
This does not work in Access 2007. It fails at "db.tabledefs(i).RefreshLink" because it attempts to use the original link.
I have not found a work round yet.

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.