About Us Tutorials Downloads Twitter Contact Us
{Search}
{Cart}
Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Reconnect linked tables on Start-up

        0 votes: *****     6,303 views      1 comment
by Allen Browne, 20 April 2005    (for Access v2+)

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: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

Give me more tutorials!

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

Comments require approval before being displayed on this page (allow 24 hours).

iTech Masters    |    VAT: GB202994606    |    Terms    |    Sitemap     |    Newsletter