[Webinar] Streamline your web hosting managementRegister Today

x
  • 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?

Thanks
Vishalarya

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

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "whateverAcesstable", "c:\whateverfile.xls", True
0
 
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
            tdf.RefreshLink 
          End If
        End If
    Next 
  Relink = True
End Function

Open in new window

0

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