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

Hi John

Yes, that has worked for me since 1994 and Access 2.0.

The only situation where I have done an automatic relinking is for an
app which has an external systems lookup database (which I can update
separately). This is assumed to reside in the same path as the app.
Thus, if the app can't see it, it is probably because both the app and
the system database have been relocated; now the app looks for it in
its own directory and if found, it relinks the tables (takes about a
second) and if not, it pops up an error box.

/gustav


> Hi Gustav,
> I think like this idea.

> Is this the gist of it (in laymen's terms)?
> -I look for the BE file at the last known location
> -if its found all should be good
> -only if there is some reason to relink -call a relinking procedure

> Also in my relinking function I started out with someone else's code to do
> this. It originally did try to "hunt down" the BE if it wasn't found in the
> last known location. As I was modifying the code to suit my needs I removed
> the hunting code for the same reasons you stated. That was way too much
> assuming for me!

> JB

>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
>> Sent: Wednesday, October 29, 2003 11:26 AM
>> 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