Solved

# Linked tables (Access) - need to automate via VBScript

Posted on 2009-04-19
Medium Priority
1,283 Views
Last Modified: 2012-05-06
Experts,

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:

http://www.visualbasic.happycodings.com/Database_SQL_Stuff/index.html

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

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.

--blulizard

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
tempTable.RefreshLink
End If

0

LVL 5

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.
0

## Featured Post

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
Course of the Month13 days, 11 hours left to enroll

#### 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.