VBA Access 2000 - Modify linked table path with VBA code

Posted on 2011-04-29
Last Modified: 2012-05-11
Dear Experts,

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


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

Question by:csehz
    LVL 47

    Accepted Solution


    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$

        set tdf = nothing

    End Sub
    LVL 9

    Expert Comment

    You can use this Add-In for excel, much easier to change linked tables/pivot tables with it.
    LVL 44

    Expert Comment

    It's often just as easy to delete the Linked table and re-create using Files, Get External Data... and follow the wizard.
    LVL 1

    Author Closing Comment

    Thanks very much
    LVL 1

    Author Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now