Linked Table Manager - better way?

[teachman]

Hello,

I believe this is my first foray into MREXCEL.COM's Access Forum. Most of my work is done in Excel. But, lately, I've been thrown in the Access pool and have some questions. One of my jobs is to take Access database applications and create export/data dumps to Excel. This particular Access application uses four separate Access databases, with one as a master that has tables that are in one of the other .mdb (or .accdb) databases so the developers created table links. Which is all well and good, except that when they come to me I have re-establish all the links. Which means using the Linked Table Manager and relinking about 50 tables. As you can imagine, doing this once or twice is bearable but pretty soon it gets real annoying.

So, I was wondering if there was any other way to re-establish links, other than using the Linked Table Manager?

Thanks,

George Teachman

 

[Joe4]

Why do you have to re-establish the links?
If they are creating new version of the data tables, just have them overwrite the current Excel files with new Excel files of the exact same name. Then you shouldn't have to re-establish any of the table links, since the file names that the Access tables are linking to aren't changing.

 

[teachman]

Sorry, I should have been more clear.

The users and I are not on the same network and have different server names. So, they zip up the four databases (one is the application) and send them to me via e-mail. I unzip the files and put them in a working space on my network. I cannot mimic their network, so I have to relink the application and its databases each time the users send the data to me to work on.

Thanks,

George Teachman

 

[bobsan42]

there are 2 easy ways:
1. one way is to replicate their network paths and file locations somehow
2. get a VPN connection and access rights to their network

the third option is to fix the links via code.
I have one that does just that but it won't work for you in it's current state. Needs a lot of re-writing to become a stand-alone thing.

It makes me wonder though: the way you describe it they have a DB split into a Front-end (some sort of GUI) and back-end (four actual data files). So they send them all to you.
If they have not done any (or only minor insignificant) changes to the front-end you can use the one you received previously - the one in which you already have the links fixed.
All you need to do is replace the old data files with the new ones.

Unless absolutely necessary to use the new front-end or unless I am wrong in my assumptions.

 

[xenou]

You can write a vba script to relink the tables, for sure (if you know the names, paths and they aren't changing all the time).

 

[bobsan42]

actually - if you have the front end and all necessary tables are linked to it - all you need is the new location of the data files.
The rest of the information is contained in the system objects table of the front-end file (MSysObjects -> Name, Type (6), ConnectString)
Plus all this can be extracted from the TableDef objects.

 

[raj08536]

Can anyone help me fixing the below code? I have linked Excel file with my MS Access Tables. Database and all excel files are in the same folder. But I will have to send these files to my co-worker (End user). How can I change link automatically using code? I found following code but it shows error.

Public Sub Relink_Table()
Dim tbl As TableDef, db As Database, strConnect As String

Set db = CurrentDb

For Each tbl In db.TableDefs
If InStr(tbl.Connect, "Portion of connect string to change") > 0 Then
tbl.Connect = Replace(tbl.Connect, "C:\Optimum\1_RM\Nov\ProjectIntegration", "C:\Optimum\ProjectIntegration")
tbl.RefreshLink
End If
Next
End Sub

 

[Micron]

I'm no admin here but it's probably better that you don't tag onto a 4 year old thread in favour of starting your own - especially if your issue isn't exactly the same.

I haven't used that syntax or those variable types. Rather more like this
Dim db As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef

...lotsa code before
tdf.Connect = ";DATABASE=" & trgtConnect

where trgtConnect is the value of a table providing connection paths. You are trying to alter the connection property of a table rather than a tableDef, and are probably making this more difficult (IMO) by hard coding the paths. I can alter a table value - you'll have to alter code. If that means your fe is an accde, you'll have to fix, compile, re-create the accde and re-distribute.

Two things that would help: knowing which line throws the error, and you using code tags with indentation for more than just a few lines. Like

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim currConnect As String, trgtConnect As String, msg As String, tblName As String
Dim result As Integer

On Error GoTo errHandler

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinkedTables", dbOpenSnapshot)
rst.MoveFirst
Do Until rst.EOF
   tblName = rst.Fields("TableName")
   Set tdf = db.TableDefs(tblName)
.....

I just skimmed and some seem to have questions about why have this. My answer would be because people do strange things, like copy db's to places they should not, or there are different drive letters among them, or worse, I forgot to change them from the development area links to the production ones!

 

[raj08536]

I have fixed the code as follow:

Sub Change_Links()

Set db = CurrentDb
Dim fso As New FileSystemObject
Dim FileName As String
Dim dbPath As String

dbPath = Application.CurrentProject.Path

For Each tdf In db.TableDefs

If tdf.Connect Like "*EXCEL*" Then

FileName = fso.GetFileName(tdf.Connect)

tdf.Connect = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & dbPath & "\" & FileName

tdf.RefreshLink

End If

Next tdf


End Sub