Linked tables (Access) - need to automate via VBScript

Posted on 2009-04-19
Last Modified: 2012-05-06

I have a database that gets copied between two different networks.  The catch here, is that I can't share data between the two networks - at all.

The DB in both locations is 100% identical, in structure / layout / etc.  The only difference being, the data that's stored in the back end is different.

Of coures, the DB's split into a front end and a back end.  And that's where this question comes into play.

Every time I update the Front End of the DB, I need to copy it over to the other network.  Since the other network has different network naming conventions / locations / etc - it takes quite a while to manually go in and update all the linked tables to their new location of the BE on the other network.

Is it possible to update the linked tables listing and location of the BE db via VBScript or similar?  That way I could put some logic into the DB and have it do it on it's own (via msgbox, command button, etc.)

I've been trying to decifer some of the the codes here:

But haven't had a ton of luck.  Any information that can be provided would be greatly appreciated.
Question by:usslindstrom
    LVL 14

    Accepted Solution

    if you're using DAO (and not ADO; if you're unsure, go to VB editor, tools > references..., and check which is higher up in the list), you can use the attached code to "relink" a table.


    Dim db As Database
    Dim tempTable As TableDef
    Dim tableName As String
    Dim pathName As String
    Set db = CurrentDb()
    tableName = "myTable"
    Set tempTable = db.TableDefs(tableName)
    If Len(tempTable.Connect) > 0 Then  ' checks if table is a linked table at all
       tempTable.Connect = ";DATABASE=c:\path\to\newdb.mdb"
       ' add ";pwd=MyPassword" to open a password protected DB
    End If

    Open in new window

    LVL 5

    Author Comment

    Thank you very much for the info!  :)

    The code worked like a champ for the linked tables.  I very much appreciate it.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    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…

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now