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,
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
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
 
csehzIT consultantAuthor Commented:
Thanks very much
0
 
csehzIT consultantAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.