[AccessD] Fw: [dba-OT] KeepingADatabaseConnectionOpeninTribble'sRelinker

Gustav,

Actually this is not quite true.  The relinker also checks for linked Excel
spreadsheets, DBXX tables, etc.  I have no idea whether these show up in the
msysobjects table where type = 6.

I sure like the idea though.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Wednesday, October 29, 2003 12:26 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Fw: [dba-OT]
KeepingADatabaseConnectionOpeninTribble'sRelinker


Hi Rocky

> I've also often wondered if it's really necessary to check all the table
> links or if just checking one to see if the database is there would
suffice.

> Or if there's even a more concise way to see if the back end is present in
> the location it's expected to be.

In my opinion there is a much nicer way to handle the relinking which,
by the way, seldom or never is necessary anyway, as Access refreshes
the links when you open your app. Only when your backend database(s)
has/have been moved or your drive mapping is changed or broken, I've
found that relinking is needed.

It is very much quicker and safer to locate and check the backend file
and act according to any errors found.

First, list the backend files:

<SQL>

  SELECT DISTINCT
    Left([Database], 512) AS DbFilename
  FROM
    MSysObjects
  WHERE
    Type = 6;

</SQL>

Then loop through this list and check if every file exists. If not,
either the file has gone (relinking won't help) or it has been moved
and relinking is needed.

If the file is found you may try to open it. If this fails, relinking
won't help because either the file is opened exclusively or it is
corrupt. In both cases other actions than relinking have to be carried
out.

For a more elaborate test - which I've never used and only is relevant
if there is a risk that somebody should be tampering with the backend
database - when the database is open, loop through the TableDefs
collection and locate every table you have linked to the frontend.
Again, if any table is not found, relinking won't help.

As you can see, the only situation where relinking is needed is if the
backend has been moved since the last time the app was used which for
a well managed network only will happen at the initial load of the app
at the client or at some planned reorganization of the network.

The "feature" of the "automatic relinker" that it - if the backend is
not found - tries to haunt down the current drive structure to locate
an arbitrary backend file is very dangerous as it will be triggered if
the workstation for some reason cannot login to the network. Users in
general have no clue where the backend is located; they are happy if
the app "works" but if the relinker has found an old local backup, the
user may be working with outdated data without knowing it which indeed
for accounting people can be a disaster.

This is for linking an mdb file. The same approach, however, can be
used when linking to a server engine.

/gustav

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com