Linked Table Manager works one at a time

[Phil Smith]

I have several databases which link to a MYSQL server.
Some of them have a dozen linked tables, some of them have 150 linked
tables. Most if not all of the tables link to the same database.

When I run the Linked Table Manager, to point the entire database from
my live production database to a day old backup database, I do so by
launching the Linked Table Manager, checking Always prompt for new
locaton, Select All, and OK.

Most of time, I will get a dialog box to choose my ODBC connection, a
little green progress bar, slowly filling up, and after some period of
time, (typically under one minute) it's all done. All of my tables have
been updated, the world is a wonderful place.

OTHER TIMES, however, I will get a dialog box to choose my ODBC
connection, hit OK, one table will get updated then back to the dialog
box to choose my odbc connector, one more table will be updated, lather
rinse repeat for every table in the database.

192 tables is my largest database, and I am sick and tired of having to
choose my odbc connection 192 times. Why doesn't it run them all at
once, like the others?

More importantly, how can I make it run them all at once, just like the
others?

 

[Joan Wild]

The key is 'Most if not all of the tables...'

If you have even one table that has a different existing link, then
you'll get prompted for every table.

Joan Wild
MS Access MVP

 

[Phil Smith]

OK, 1) That has not been my experience. What happens is it updates all
of the tables, going dow the list, until it hits one that does not
conform, then hits me up for the new locatiion. Continues updating
until it fails to find a table in the location I specified, and brings
it up again.

2_ In the case of the 192 table database that I am trying to update, all
of the linked labels come from the same database, so that is not the
problem.

 

[Jeanette Cunningham]

Hi Phil,
I experience this problem sometimes, I haven't tracked down the reason.
I avoid all the 192 clicks by deleting all the linked tables, doing a get
external data, link to existing data, where I can usually find a select all
button to select all the tables in one hit.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Roger Carlson]

I would roll my own re-linking process. On my website are two small sample
databases, which, if combined, should get do what you need.

LinkTables.mdb
(http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=334)
illustrates how to programmatically link tables from a user designated
database. It's pretty simplistic, as it only re-links one table, but the
same process can be used for multiple tables.

RelinkOnOpen.mdb
(http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=286)
illustrates how to automatically relink the tables if the database has been
moved. That's not what you want to do, but the part here that is useful is
it programmatically marches through all of the tables and relinks them to
the designated database (which happens to be in a subfolder)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L