Solved

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

Posted on 2009-04-19
1,130 Views
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

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.

--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"

End If

0

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

## Featured Post

### Suggested Solutions

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  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) 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…