Use VBA code to relink / change linked tables.

Posted on 2006-04-27
Last Modified: 2008-01-09
Is there anyway I can use VBA code in MS ACCESS to remove a linked table and connect a linked table?

I need the linking code (or procedure if it's big enough) to go in the following:

UNLINKTABLE("tbl_main", "C:\date\20060426\exampe.mdb") 'unlink yesterday's table, inorder to get new data.

If Exists "C:\date\20060427\exampe.mdb" then 'if file is available
     LINKTABLE("tbl_main", "C:\date\20060427\exampe.mdb") 'link today's table
Else 'if file is not available
     LINKTABLE("tbl_main", "C:\date\20060426\exampe.mdb") 'link to yesterday's table
End If
Question by:Marcarobinson
    LVL 19

    Expert Comment

    Dim db As DAO.Database, tbl As DAO.TableDef,s as string
       Set db = CurrentDb
       set tbl = db.TableDefs("tbl_main")
    if len(dir("C:\date\20060427\exampe.mdb"))>0 then
            s = ";DATABASE=" & "C:\date\20060427\exampe.mdb"
            s = ";DATABASE=" & "C:\date\20060426\exampe.mdb"

    end if
    tbl.Connect = s

    Author Comment

    I get the error "User-defined type not defined" and it highlights the "db as DAO.Database" part of the code.
    LVL 19

    Accepted Solution

    you need to add DAO library

    Open code ->Tools->References->Check Microsoft DAO 3.6

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now