VBA code to get column data from another worksheet.

Hi,

I have a client who has his frontend and backend always in the same folder but sometimes copies both files to different computers and thus different paths. So we relink the backend tables every time he starts the application to the folder path of the frontend. Your problem seems similar and could probably be solved = automated with a similar technique:

1. Create a new VBA module in the frontend.

2. Copy this function into the module:

Public Function fctLinkedTablePaths()

    On Error GoTo myError

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strDbFile As String
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
    
        If tdf.Connect <> "" Then
            strDbFile = Mid(tdf.Connect, InStrRev(tdf.Connect, "\"))
            tdf.Connect = ";database=" & CurrentProject.Path & strDbFile
            tdf.RefreshLink
        End If
      
    Next tdf
    
myExit:
    Exit Function

myError:
    Select Case Err.Number
        Case 9999
            'trap specific errors
        Case Else
            MsgBox "Exception No. " & Err.Number & ". " & Err.Description
            Resume myExit
    End Select

End Function

3. Create a new macro called: AutoExec

4. In the macro choose the action: RunCode

and as Function Name: =fctLinkedTablePaths()

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon