In a controlled environment, i.e. your own organization, you can use either a
fixed drive letter and path, Z:\Path\data.mdb, or the UNC and path, \\server
name\path\data.mdb. You can set this up on your own system and so long as
everyone has the same network drive letter or sees the same server you can
distribute your FE as is.
If you're dealing in a non controlled environment then you'd want to see if a
particular table can be opened. If fine then exit. If not then locate a
previously stored path, either in an INI file as I do, a registry entry or
otherwise, and then relink the tables. See the
Access Web Relinking
Tables. Use the API call as linked in that posting instead of using
the Common Dialog control as you will have lots of version problems on other
If that previously stored path no longer works or doesn't exist then use the
File Open API calls to locate the backend end. Once successfully located store
this location as previously mentioned so the users won't have to find the
location in the future. Especially if you give them a new front end. Do not
store this location in either the FE or the BE as these won't be available the
next time you need them. I prefer using an INI file as that is easy to locate
and understand if you ever have to update it manually.
Note that refreshing links to tables can be dramatically sped up. Even in
Access 97. Once the first table has been refreshed open a recordset based on
that table. Continue refreshing. Once finished refreshing close the
recordset after you've opened a bound form for performance
Michael Kaplan has posted the following:
"One thing you can look at is the
deletion/recreation of links. I have been amazed at how much stuff gets cached in
the links for tables, and also how much of what is cached is based on usage
patterns (so that if the two users make use of a link two different ways, the
info might be different). Deleting and recreating the link can often resolve
these weird cases.
The interesting "features" (bugs) in linked tables that have to do with the way
that they are implemented from a low level architectural standpoint is something
I have thought about doing an article on, but its hard to really imagine that it
would be something the world would want to read about (and I get criticized
often for not writing enough "real world" stuff)."
Thus I'd suggest that if you rename fields in tables or insert new fields in
the middle of a table, rather than at the end, that you recreate the links
rather than refreshing.
At one client I use a variation of the drive letter approach and don't bother
to relink the tables. I have two three line batch/cmd files. In one I use the
SUBST command to create a drive letter which is the same as the server drive
letter. In the other I use the NET USE command to link to the drive letter on
the server. In both cmd files I delete the SUBST and the NET USE first.
I then copy the clients live BE data MDB to my system every day or two to ensure
I'm working with a reasonably current copy of their data. I can then do any
testing I want on my own system without affecting the live BE. This
includes accidentally deleting the entire contents of one transaction table when
I forgot to put a WHERE clause on a DELETE query. <smile> I run
the above cmd files to switch between using the live BE and my copy of it. If I need
to update the backend's tables, fields and/or relationships I wait until the end
of the day when everyone is out of the system. Once added I copy it down again.
I also have an always open form in the corner which, for me only, is pale yellow
if the BE is on a local hard drive. However if the BE is on a network drive this
form becomes the reddest possible value. This makes an excellent visual warning
for me to be very careful what I do. <smile> I used the code at
Determine the Type of Drive Using Win32 - 161300.