Relinking to multiple back end files

  • Thread starter Thread starter Virgil
  • Start date Start date
V

Virgil

I've got a front end (Access 97 & 2000) that links to
several back end data files (all DAO databases). The front
end's location may change from site to site, so I want to
develop VBA code that will alert the user to reconnect to
each of the sources upon opening the front end splash
screen. I've tried Dev Ashish's method (fRefreshLinks)
found in The Access Web. Great function! But it only works
if all linked tables have the same back end source.
Pulling up the Linked Table Manager (via Docmd.RunCommand)
isn't my best option either because I can't vouch for the
computer savvy of my users. The easiest method for the
user is for them to supply the new location (via a prompt
screen) for each unlinked back end source. I've thought
about building a recordset test for each distinct source.
Upon error, the user would be promted to supply the new
location. Tht method also probably would entail getting
the table definitions for each of my 90+ tables, appending
the table names and source paths into a temp table and
providing a list box (multi-select) of tables to reconnect
for each distinct source.

Just wanted to know if anyone already has a similar
function on how to relink to multiple back ends without
having the user pull up the Linked Table Manager.
 
I examine the connect string for each linked table to
find the database name. Are your data files in different
folders? When you say 'The front end's location may
change from site to site' do you mean the Back End? or
are you working with links between the front end files?

(david)
 
Actually, I want the back ends to be housed in the same
folder either on a network or an external drive (for non-
dockable, non-networked laptops), so both the back end and
front end locations may change from PC to PC, site-to-
site. But thinking of worse case scenario, the back ends
may actually wind up in different folders. I'm exploring
getting Name, Database, and ForeignName from MySysObjects,
exporting those to an Excel spreadsheet(due to the
Database field being a memo type), attaching the xls as a
temp table, then grabbing the linked tables info for each
for each database.
 
FWIW: I expect all my BE files to be in one place, so I have
an easier task. I'm using DAO to re-link, so I get the
connect information from the tabledef, rather than looking
at MSysObjects tables. I don't see any objection to using
Excel for your reference table, although I guess I would use
a local table of BE files rather than scanning or listing
the tables. I guess you will want to put each FE database
on a different page in the spreadsheet. I don't see any need
for a list box to select tables: All the user has to do is
select a file location for each of your BE files: your code
will identify which tables should be re-linked.

(david)
 
Back
Top