If the actual names and locations of the linked tables aren't changing -- just
their structures -- it's fairly easy to automate. What you need is a loop to
refresh the links. There's a very complete example here:
You might not need to do some of the housekeeping in that example. For instance,
the main function prompts the user "Are you sure you want to reconnect all Access
tables?" The function which actually refreshes the links checks to be sure that the
specified data source is where it's supposed to be, and if not gives the user a
dialog box to browse to it. And so on.
Note that the example refreshes ALL linked tables. If you just want to refresh the
MySQL data sources' links, you can examine each Connect string to see if it
contains some particular substring (like "MySQL" :). If it does, refresh it; if it
doesn't, proceed to the next linked data source.
Given that code, all you need to decide is what needs to trigger the refresh. You
probably don't want to refresh all tables every time the database opens; maybe just
a command button on some form which only you (or whoever the DBA is) can access.
----- Original Message -----
From: "Bill Schwab" <BSchwab@stripped>
Sent: Monday, August 09, 2004 10:08 PM
Subject: Access script to link many tables?
> I gather from reading here that an Access database used to link to MySQL tables
must be reconstructed if the table structure changes. I have a database with quite
a few tables, and dread doing the job manually. Do any of you have a VBA (or
whatever they call it this week) script that does such a thing?
> Wilhelm K. Schwab, Ph.D.
> University of Florida
> Department of Anesthesiology
> PO Box 100254
> Gainesville, FL 32610-0254
> Email: bills@stripped
> Tel: (352) 846-1285
> FAX: (352) 392-7029