Update links to multiple backends

[jkearns]

I have an Access frontend/backend combination that gets moved around a
lot. To deal with this, I've been using the code found at:

http://www.mvps.org/access/tables/tbl0009.htm

It runs every time the frontend is opened to check and make sure it is
connected properly to the backend (if not, it opens a dialogue box to
choose the new location of the backend). This all works great - until
now.

I now want to include a table from another data source for the frontend
to link to. When this code runs, I am forced to choose one of the
datasources. No matter which one I choose, the entire thing errors
because it cannot link to *all* the tables.

Does anyone know how to refresh a list of linked tables from more than
one datasource?

Thanks!
JK

 

[Klatuu]

Below is an excerpt from a relinking routine I use. It looks for the name of
the database to determine which path to use for linking. Each path has been
entered in a separate text box on the form.

For Each tdf In dbs.TableDefs
intcount = intcount + 1
Response = SysCmd(acSysCmdUpdateMeter, intcount)

If Len(tdf.Connect) > 0 Then
' Its a linked table. Re-link

datafile = InStr(1, tdf.Connect, "CISCMSdata")
localfile = InStr(1, tdf.Connect, "LocalData")

If localfile > 0 Then
tdf.Connect = ";database=" & Me.localpath
tdf.RefreshLink
LocalCnt = LocalCnt + 1

ElseIf datafile > 0 Then
tdf.Connect = ";database=" & Me.datapath
tdf.RefreshLink
DataCnt = DataCnt + 1

End If
Else
' Not a connected table; don't do anything.
End If
Next tdf

 

[John Nurick]

Hi JK,

If you haven't got it sorted yet you may like to look at
http://www.mvps.org/access/tables/tbl0012.htm which handles multiple
data sources.

 

[JK]

Thanks Klatuu and John,

I tried both of your suggestions, but neither seem to work properly. I
also spent a bit of time with the original code and added a bit to it.
I've got it to the point where it looks for more than one backend (if
necessary), but I'm running into a new problem that maybe you might
know the answer to.

I will have many cases where the table name in the front end of the
database is different than the table name in the backend. When I run
code that looks like this:

Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.NAME)
End With

It errors as the table names are different (strTbl is the name of the
table on the front end and strDBPath is the new path to the backend).
I tried adding a line in the With statement that looked like:

..SourceTableName = strSourceTbl

Where strSourceTbl was a strnig that captured the Source Table name
from the collection of table properties, but it told me I cannot set a
property once the object is part of a collection (Err# 3268). Any idea
on how to get around this?

Thanks!
JK

 

[Klatuu]

I think maybe you misunderstood the code I posted.
These lines:
datafile = InStr(1, tdf.Connect, "CISCMSdata")
localfile = InStr(1, tdf.Connect, "LocalData")

refer to the mdb file name, not a table name. the mdb file name is
contained in the Connect property of the tabledef (tdf), so when you have
more than one back end, you have to determine which backend the tabledef is
linked to. The code above determines that by using the InStr function to see
if the mdb file name is contained in the Connect property.

I know the code I posted works, it runs at least once a month.

 

[JK]

Thanks. I figured out what I had done wrong and it is working now.

I appreciate the help.

JK