General Sanity Check on Programmatic Resetting of Links

[LarryP]

We have several Access 2003 databases that link to numerous source files on a
workgroup server at our main location. Unfortunately, people at some of our
sublocations can't make use of these databases because of bandwidth issues:
although they can "see" the main location server and the relevant files, when
they try to run the databases things get really, REALLY slow. Screens that
display in 10-15 seconds here take 20 minutes there, etc. The IT guys say
not to hold our breath waiting for a hardware fix, it ain't gonna happen any
time soon.

So, we're contemplating copying all the necessary files, currently about 2GB
worth, to a server at the remote location once a week. Doing that, though,
would involve resetting a couple dozen links in the front ends of the various
databases. For obvious reasons we don't want to have to maintain multiple
front end versions, so we're envisioning a procedure in the common front end
that says "If I'm logging on at the home office, set all the links to
xxxxxxx, but if I'm logging on at sublocation A, set the links to yyyyyyyy,
...." etc. The necessary data about what the links should be for each
location would be stored in a table.

Has anybody tried anything like this, and if so, with what result? What
problems might we encounter, and what suggestions might anyone offer to make
the transition to this solution as painless as possible? Just hoping for
general guidance before we put a lot of effort into this.

 

[Steve McLeod]

You might check this for your performance issues.
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

I have a similar problem. I'm considering replication and need the same
advice before going ahead.

 

[John Nurick]

Hi Larry,

You sound perfectly sane<g>. Lots of databases do this sort of thing.

Will all the front ends be on desktop machines and not move once
installed, or will some of them be on laptops that move between
offices?

If all-desktop, you could just put a "FirstRun" record in your
"Settings" or "Defaults" table. Check its value when the database
opens, and only if it's true call the routine that asks for the
location and then reads the "Source files" table and re-links the
linked tables, then set FirstRun to false.

If you have to deal with laptops, you'll need to test on startup
whether the source files are where expected. See
http://www.mvps.org/access/tables/tbl0009.htm and
http://www.mvps.org/access/tables/tbl0007.htm for some sample code.

 

[LarryP]

Don't know the answer at this point. I think the large majority will be
desktop PCs, but some of the managers may be on laptops. I see the value of
the "first run" thing so the link-resetter doesn't need to run with every
startup. FWIW, since all the databases involved are read-only, looking at
data that gets downloaded from the mainframe over the weekend but not
manipulating it in any way, we've even got a "Plan C" that would put all the
files on each user's hard drive at the start of his first Monday run. That
would no doubt make everything run lightning fast, but (1) we're not
comfortable with the assumption that every drive will have 2-3gb free for
that purpose, and (2) pulling all those files across that itty-bitty 10-Base
T wire from the home office could be a real ordeal. If 50 different remote
users all try to do it simultaneously, the whole remote site might go up in
smoke!

 

[John W. Vinson]

pulling all those files across that itty-bitty 10-Base
T wire from the home office could be a real ordeal

For that matter, running an Access frontend/backend setup across 10baseT
sounds like a Very Bad Idea (as you're finding!)

Any chance of going to SQL/Server on your host, with ODBC connections to your
frontends, and forms designed to pull the fewest possible records? Or perhaps
using Windows Terminal Server or Citrix Server so the users are just using a
"dumb" terminal, and the Access parts are all on the same fast stable LAN?

John W. Vinson [MVP]

 

[LarryP]

Could happen some day, but only after Big IT studies it to death, somebody
comes up with the bucks, etc., etc. Right now we're just looking for a
workable bandaid so the remote site people can see the same stuff on their
screens as the manager who's calling them from HQ and beating them up about
it. If it can't be made to work on a LAN, next best thing we could think of
is to give them all the data once a week so they're running it locally.
Fortunately it's all read-only for them; if it weren't the replication issues
would be a show-stopper.

 

[John W. Vinson]

If it can't be made to work on a LAN

It can... if it's a fast, stable LAN. Not on a WAN, not over wireless, in my
experience; and 10baseT is possible but very, very iffy.


John W. Vinson [MVP]

 

[LarryP]

Typo -- should have been "WAN." If nobody here points out any insurmountable
problems, we'll probably set it up so at some agreed-upon time each Monday,
all the current week's files will be moved to their server, and everybody
there who uses the databases will be linked to that server rather than the
home office server. Trick will be to set up the appropriate link adjustment
routine in the various front ends, since there are several of these remote
sites and the server path will be different for each.

 

[John Nurick]

If nobody here points out any insurmountable
problems, we'll probably set it up so at some agreed-upon time each Monday,

A good time might be 1 am, using a scheduled task running on a server
to copy the files to the various destinations.

For greatest safety, have the script check for the existence of the
..LDB locking files in both source and destination folders before
copying the corresponding .MDBs. The existence of a .LDB file
indicates that some user has failed to log out of the database and
therefore that it may be in an inconsistent state.