How can a relative path specify a linked table in Access 2007?

Question

I have a front end and back end of an Access database. The front end references linked tables and I need to do a relative link instead of an explicit one i.e. "../database" is referenced instead of "address/database"

Is it possible to do this, or must I specify the absolute path?

Motherinlaw

How ridiculous that Access doesn't support relative paths out of the box. How is someone supposed to ship a client a split database with absolute paths in it?! – Uncaredfor

The limitation is likely due to Access being multi-user - because multiple users can use the same file and there is file locking - then a full qualified path is required. The simple solution is on startup your front end checks if the back end is available (and that check can be relative). If the link is wrong, then your code simply re-links on startup. In effect this means your application will run just fine if it is moved. – Lapsus

@MatthewLock each user needs their own copy of the front end. There can only be one copy of the backend. By this definition the relative path from each FE to the BE is going to be different. You need to know where the client will store the back end. Then put that path in to the FE before you ship. Otherwise you would need to provide a method for the client to set the BE path before they distribute the FEs. It could be built in to your FE deployment software. – Selassie

Answer 1

As far as I know, your TableDef's Connect property requires an absolute path. If I'm wrong on that point, I hope someone will tell how to create a linked table using a relative path.

Take a look at Armen Stein's free utility to manage your table links: J Street Access Relinker

Smoker

Answer 2

Tables linked to files (such as mdb, accdb, dbf, etc.) require absolute paths in their connection strings.

However there is a workaround: during the database startup you can use vba to redefine the the links to match the directory of the current database instance.

(The code below has not been tested / debugged)

Private Sub RelinkTables()
    Dim oldConnection As String
    Dim newConnection As String

    Dim currentPath As String
    currentPath = CurrentProject.Path

    Dim tblDef As TableDef

    For Each tblDef In CurrentDb.TableDefs
        oldConnection = tblDef.Connect

        ' Depending on the type of linked table
        ' some string manipulation which defines
        ' newConnection = someFunction(oldConnection,currentPath)

        tblDef.Connect = newConnection
        tblDef.RefreshLink
    Next tblDef
End Sub

Daves

Answer 3

I have tried some of the answers above, especially the answer of Martin Thompson which I got some errors with, and thus modified it as follows:

Public Function reLinkTables() As Boolean
On Error GoTo ErrorRoutine
Dim sMyConnectString        As String
Dim tdf                     As TableDef
Dim db_name                 As String
    ' The Main Answer is by Martin Thompson
    ' Modified by Dr. Mohammad Elnesr
    'We will link all linked tables to an accdb Access file located in the same folder as this file.
    'Replace the DATA file name in the following statement with the name of your DATA file:
    sMyConnectString = ";DATABASE=" & CurrentProject.Path & "\" 
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
            'It's a linked table, so re-link:
            'First, get the database name
            db_name = GetFileName(tdf.Connect)
            ' Then link the table to the current path
            tdf.Connect = sMyConnectString & db_name
            tdf.RefreshLink
        End If
    Next tdf


ExitRoutine:
    MsgBox "All tables were relinked successfully"
    Exit Function
ErrorRoutine:
    MsgBox "Error in gbLinkTables: " & Err.Number & ": " & Err.Description
    Resume ExitRoutine
End Function

Function GetFileName(FullPath As String) As String
    Dim splitList As Variant
    splitList = VBA.Split(FullPath, "\")
    GetFileName = splitList(UBound(splitList, 1))
End Function

After fininshing this, Goto Access Ribon>Create>Macro From the dropdown select "RunCode", then in the function name type "reLinkTables" which we typed here. Then save the macro with the name "AutoExec". Every time you open the database, all the linked tables will be relinked to the original path. This is very useful if you put your databases in a portable media.

Modlin

Answer 4

The following code has been tested in the Form_Load event of the form listed in the "Display Form" option for the database; that is the form that loads whenever the database is opened. This code could also be called from the AutoExec macro for the database:

Private Sub Form_Load()
Dim strOldConnect As String
Dim strNewConnect As String
Dim intSlashLoc As Integer
Dim intEqualLoc As Integer

Dim strConnect As String
Dim strFile As String
Dim strCurrentPath As String

strCurrentPath = CurrentProject.path

Dim tblDef As TableDef
Dim tblPrp As Property

For Each tblDef In CurrentDb.TableDefs
    Debug.Print tblDef.Name
    If tblDef.Connect & "." <> "." Then

        strOldConnect = tblDef.Connect
        intEqualLoc = InStr(1, strOldConnect, "=", vbTextCompare)
        strConnect = Left(strOldConnect, intEqualLoc)
        intSlashLoc = InStrRev(strOldConnect, "\", -1, vbTextCompare)
        strFile = Right(strOldConnect, Len(strOldConnect) - intSlashLoc)
        strNewConnect = strConnect & strCurrentPath & "\" & strFile

        tblDef.Connect = strNewConnect
        tblDef.RefreshLink
    End If

Next tblDef
End Sub

Perse

I found this code ran fine without the Dim tblDef As TableDef line. It was causing a "User Defined Type not Defined" error, which couldn't be fixed by prefixing "DAO." to "TableDef" – Thymic

Answer 5

Here is a simple routine that worked for me:

Public Function gbLinkTables() As Boolean
On Error GoTo ErrorRoutine
Dim sMyConnectString        As String
Dim tdf                     As TableDef

    'We will link all linked tables to an accdb Access file located in the same folder as this file.
    'Replace the DATA file name in the following statement with the name of your DATA file:
    sMyConnectString = ";database=" & CurrentProject.Path & "\Loan-Tracking-Data.accdb"
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
            'It's a linked table, so re-link:
            tdf.Connect = sMyConnectString
            tdf.RefreshLink
        End If
    Next tdf


ExitRoutine:
    Exit Function
ErrorRoutine:
    MsgBox "Error in gbLinkTables: " & Err.Number & ": " & Err.Description
    Resume ExitRoutine
End Function

Foulmouthed

Answer 6

you can make a "calculated" field.. works for me in Office Access 2016

"F:\Komponenten\Datenbank\Bilder\" & [Kategorie] & "\Pinout\" & [Bezeichnung] & ".jpg"

maybe there are better solutions, see images

calculated path result

Domella

The question already has an accepted answer - and this doesn't appear to add anything to that? The OP was asking about whether they can use relative paths. – Kristiankristiansand