Linked tables (Access) - need to automate via VBScript

Posted on 2009-04-19
Medium Priority
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

bluelizard earned 2000 total points
ID: 24182243
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


Author Comment

ID: 24189793
Thank you very much for the info!  :)

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

749 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