[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

VBA Access 2000 - Modify linked table path with VBA code

Dear Experts,

I have an excel table linked to Access database, in the table properties it appears like this below:

Excel5.0;HDR=YES;IMEX=2;DATABASE=D:\Folder\routing.xls;TABLE=Routing_PLF5_0115$

Of course but this can not be changed in table Design View, can you please advise could it be changed by VBA code?

For example I would like to have the file linked from C:\My documents

thanks,
0
csehz
Asked:
csehz
1 Solution
 
Dale FyeCommented:

Private Sub Relink()

    Dim tdf as DAO.Tabledef

    set tdf = currentdb.tabledefs("Routing_PLF5_0115$")    'this should be the local name you see in the database window

    tdf.Connect = Excel5.0;HDR=YES;IMEX=2;DATABASE=C:\my documents\routing.xls;TABLE=Routing_PLF5_0115$
"
    tdf.refreshlink

    set tdf = nothing

End Sub
0
 
hitsdoshi1Commented:
You can use this Add-In for excel, much easier to change linked tables/pivot tables with it.

http://www.contextures.com/xlPivotPlayPlus01.html
0
 
GRayLCommented:
It's often just as easy to delete the Linked table and re-create using Files, Get External Data... and follow the wizard.
0
 
csehzAuthor Commented:
Thanks very much
0
 
csehzAuthor Commented:
GRayL I know but unfortunately there  are more tables, moreover daily changing file names like file2904.xls so also would like to build a logic in vba to follow those changes with the link

0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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