Stuart Sanders
stuart at pacific.net.hk
Thu Oct 30 00:36:38 CST 2003
I have gone part of the way with this. During startup, I have been doing the following: -initialise a function containing static db array that maintains a connection to linked DBs -go through the linked tables and add each linked db to static db collection once -check the links to one table on each found linked db -relink if necessary -terminate and clean up the static db array -continue with project init/startup I like Gustavs sql listing of linked DBs. I'd never thought about doing it that way, and I think I will modify how I do it. I am thinking though that a further test is still probably required to keep it as user friendly as required. Assuming the file exists, but is corrupt, it would be nice if the db could have the option to automatically attempt repair the BE and then fail gracefully if failure occurs. Stuart > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Gustav Brock > Sent: Thursday, 30 October, 2003 2:36 AM > To: Access Developers discussion and problem solving > Subject: Re: [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 > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >