Access Linked Tables in VBA code

krishnarao

Registered User.
Local time
Today, 11:39
Joined
Jun 11, 2007
Messages
12
My Database has a linked text file which is in the same path of mdb file. when i copy my mdb file in different path (along with the text file) it should access only the file in that path. how to change the path of the linked file through VBA or is there any other way to do it?
 
Something like that perhaps...
CurrentProject.path & "\textfile.txt"
 
code LInked Tables in code

when the DB is opened i need to change the linked table path to the current path. so how can i would access the table object to change the physical path of the table?
 
I don't understand...
The tables you are talking about, are they the textfiles you talked about earlier or is this another question?
If this a split database with front end and back end, why isn't the back end located on the server? Else, why did you split the database if it is all installed on a single computer?
 
I believe he is trying to change the path stored in the Linked Table Manager via Code, not the path of a recordset object. If this is true, it should be something like:

Code:
Public Function RefreshTableLinks()
    Dim tbl         As DAO.TableDef
    Dim db          As DAO.Database
    Dim sPath       As String
    
    Set db = CurrentDb
    sPath = CurrentProject.Path & "\"

    
    For Each tbl In db.TableDefs
        If tbl.Name = "<Name of Text File Table>" Then
            tbl.Connect = ";DATABASE=" & sPath & "<txt file name>.txt"
            tbl.RefreshLink
        End If
    Next
End Function
 
Or try this:

Code:
Public Function RefreshTableLinks()
    Dim tbl         As DAO.TableDef
    Dim db          As DAO.Database
    Dim sPath       As String
    Dim sFileName   As String
    Dim sConnect    As String
    
    Dim iBeg        As Integer
    Dim iEnd        As Integer
    
    
    Set db = CurrentDb
    sPath = CurrentProject.Path

 'Sample connection string: _
    sConnect = "Text;" & _
               "DSN=RV_DAILY_TEST Link Specification;" & _
               "FMT=Fixed;" & _
               "HDR=NO;" & _
               "IMEX=2;" & _
               "CharacterSet=437;" & _
               "DATABASE=" & sPath & ";" & _
               "TABLE=RV_DAILY_TEST#txt"
   
    
    For Each tbl In db.TableDefs
        If tbl.Name = "RV_DAILY_TEST" Then

            sConnect = tbl.Connect                      'Store connection to string for faster processing
            iBeg = InStr(sConnect, "DATABASE=")         'Get the location of the filepath
            iEnd = InStr(iBeg, sConnect, ";")           'Get the location of where the path ends
                            
            tbl.Connect = Left$(sConnect, iBeg - 1) & _
                          "DATABASE=" & sPath & _
                          right$(sConnect, IIf(iEnd = 0, 0, Len(sConnect) - iEnd))
            tbl.RefreshLink
        End If
    Next
End Function
 

Users who are viewing this thread

Back
Top Bottom