[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4716
  • Last Modified:

Automatically update linked tables MS Access

I have a tool that references linked tables from 2 other tables.  I know both the locations of the MDBs and the table names where they reside.  I am further using a macro to link the tables by using the currentproject.path variable with the assumption that all MDBs will reside in the same folder.  However, when I copy the MDBs to another folder, after linking the tables, I cannot see the data in the linked tables.  Is there anyway to automate the update linked tables to the new folder contained in "currentproject.path" in VBA?


2 Solutions
delete the existing linked table and then recreate using the DoCmd.TransferSpreadsheet method, for eg:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "whateverAcesstable", "c:\whateverfile.xls", True
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can "refresh" your table links by using the function below. If you'd like to link it to a database in the same folder as the currentdb, then do this:

Relink CurrentProject.Path & "\YourDBName.mdb"

Public Function Relink(PathToDatabase As String) As Boolean
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb
    'Loop through the tables collection
       For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSYS" Then
          If Len(tdf.Connect) > 0 Then 
            tdf.Connect = ";DATABASE=" & PathToDatabase
          End If
        End If
  Relink = True
End Function

Open in new window


Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now