Redirecting Linked Tables

[doodle]

Greetings all.

Windws xp, Access 97.

I am running a split db with multiple users in regional offices. The
connection between servers is too slow for the other offices. So I have
a batch file that runs every night to copy the tables and front end
over to their servers. (The other offices are all Read Only anyway.)

All is well with the batch file. The problem is the linked tables. For
example, for Chicago:

1. I copy the front end database file to a folder on my server named
"Chicago".
2. I open the front end and use the linked table manager to redirect
the links to their server.

-This is very time consuming considering that I have to do this for
every table and for ten different offices.
-After I have made the updates to the table links, I run my batch file
which copies all of the copies over to all of the other servers.

There has to be a better way to redirect the table links.

help.

-doodle

 

[Graham Mandeno]

Hi Doodle

You can write VBA code to relink tables. Check out the help topic for the
DAO Connect property for some example code.

One solution for you would be to attempt to open a key table when the
frontend opens and, if it fails, run code to relink all the tables.

 

[George Nicholson]

Um, sorry if I missed something, but why is it necessary to copy the front
end every night? If properly split, the front end should be static on a
day-to-day basis unless there have been design changes to Forms, Reports,
queries or code. (I can understand occasionally, every night?, no.)

In which case, it is just a matter of distributing the updated backend every
night. Assuming the backend name & location doesn't change, no relinking is
necessary in the fe since it still points to the same file path & name as
yesterday.

If it really is necessary to copy the fe every night, maybe you can set
yourself up so that Tony Toew's free FEUpdater would be of use:
http://www.granite.ab.ca/access/autofe.htm
Maybe something like: Set up the proper link in *one* FE "master" copy per
regional server. FEUpdater puts a copy of that "master" on each user's local
drive (and opens it) when they use the designated shortcut for your app
every morning.

If you need some sample "auto-relinking" code:
http://www.mvps.org/access/tables/tbl0009.htm

HTH,

 

[doodle]

Hi George.

1. I copy the front end over every night because I make changes to it
constantly. So I just have the batch file copy it. I already use Tony's
code to update the front end.

2. Your suggestion of setting up one with a proper link is the problem.
That is what I am doing now. The problem is that I make changes to my
master which has the tables mapped to my server. When I make changes, I
have to redirect those links for every server.

Thanks for the tip Graham. I will check out the code and post back if I
need more help.

-doodle

 

[George Nicholson]

You could use VBA's GetSetting, SaveSetting methods to write an entry
containing the proper backend path to each user's registry. When your
"auto-relink" code fails to establish a link based on the last used table
connection, have it try the path from the registry entry. Make sure to Save
the setting whenever relinking is successful.

This way you'd only have to deal with the proper link problem one more time
(assuming all users open the db so that they have a "good" registry entry
created). After that it would be automatic.

HTH,