Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Thu Oct 30 23:03:57 CST 2003
John: I'm beginning to think you're right. I always took it as an article of faith to check all the tables - started with that chunk of code in the ADH97. I think I've got to write a new checker/relinker routine tomorrow. Rocky ----- Original Message ----- From: "John Bartow" <john at winhaven.net> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Wednesday, October 29, 2003 8:22 AM Subject: RE: [AccessD] Fw: [dba-OT]KeepingADatabaseConnectionOpeninTribble'sRelinker > Hi Rocky, > I just check for one table. If you have more than one linked source then, of > course, this wouldn't be wise. > > I have a function which accepts a table name as a parameter and check the > link. I guess it is making the assumption that if one table is OK they all > are - it's usually not a good thing to assume - but I haven't heard of any > other reason this idea is flawed. It will be interesting to watch this > thread. > > JB > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin - > > Beach Access Software > > Sent: Wednesday, October 29, 2003 10:05 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Fw: [dba-OT] > > KeepingADatabaseConnectionOpeninTribble'sRelinker > > > > > > John: > > > > 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. > > > > Rocky > > > > ----- Original Message ----- > > From: "John Colby" <jcolby at colbyconsulting.com> > > To: "Access Developers discussion and problem solving" > > <accessd at databaseadvisors.com> > > Sent: Wednesday, October 29, 2003 7:32 AM > > Subject: RE: [AccessD] Fw: [dba-OT] Keeping > > ADatabaseConnectionOpeninTribble'sRelinker > > > > > > > Rocky, > > > > > > I too have taken to disabling the relinking unless needed. I > > try to map a > > > drive on every computer such that the links just work. I wrote an email > > re > > > this subject a month or two ago. > > > > > > John W. Colby > > > www.colbyconsulting.com > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin - > > > Beach Access Software > > > Sent: Wednesday, October 29, 2003 9:52 AM > > > To: Access Developers discussion and problem solving > > > Subject: Re: [AccessD] Fw: [dba-OT] Keeping A > > > DatabaseConnectionOpeninTribble'sRelinker > > > > > > > > > John: > > > > > > Well, it's working and I'm going to spread it around to all of > > my client. > > > Thank you for the code. > > > > > > I'm going to one client today where the relinking is very slow. > > Although > > at > > > most sites I've disabled the automatic relinking on opening the app. I > > > usually put the relinking call into the OnClick event of the logo on the > > > splash form. So I can have them force a relink if it becomes necessary. > > > > > > Best, > > > > > > Rocky > > > > > > ----- Original Message ----- > > > From: "John Colby" <jcolby at colbyconsulting.com> > > > To: "Access Developers discussion and problem solving" > > > <accessd at databaseadvisors.com> > > > Sent: Tuesday, October 28, 2003 8:15 AM > > > Subject: RE: [AccessD] Fw: [dba-OT] Keeping A Database > > > ConnectionOpeninTribble'sRelinker > > > > > > > > > > Rocky, > > > > > > > > That will depend to some extent on how many people are in the database > > and > > > > how many tables are being checked. It seems that the more > > people in the > > > > database, the longer the ldf lock file negotion takes. Thus > > the speedup > > > > with only one person in isn't dramatic. The speedup with 20 in can be > > > very > > > > dramatic. > > > > > > > > John W. Colby > > > > www.colbyconsulting.com > > > > > > > > -----Original Message----- > > > > From: accessd-bounces at databaseadvisors.com > > > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of > > Rocky Smolin - > > > > Beach Access Software > > > > Sent: Tuesday, October 28, 2003 10:59 AM > > > > To: Access Developers discussion and problem solving > > > > Subject: Re: [AccessD] Fw: [dba-OT] Keeping A Database Connection > > > > OpeninTribble'sRelinker > > > > > > > > > > > > John: > > > > > > > > Thanks for the code. > > > > > > > > It works well but not a dramatic speed improvement. > > > > > > > > I made a copy of ts_tableLinks and called the old function > > > > tsCheckTableLinksOriginal and the new one tsCheckTableLinksNew. > > > > > > > > (I didn't use the conditional compile. Got an error - Next without > > For - > > > > and there's about 12 nested If/Thens, so I just replaced the call > > > > > > > > fLinkIsValid(mtbl.Name) > > > > > > > > in the original with > > > > > > > > fLinkIsValidNew(mdb, mtbl.Name) > > > > > > > > in the new version.) > > > > > > > > Then I put both calls in the opening form: > > > > > > > > tsCheckTableLinksOriginal > > > > tsCheckTableLinksNew > > > > > > > > The Original takes about 7 seconds and the New takes about 5. Is this > > > about > > > > right? > > > > > > > > Regards, > > > > > > > > Rocky > > > > > > > > ----- Original Message ----- > > > > From: "John Colby" <jcolby at colbyconsulting.com> > > > > To: "Access Developers discussion and problem solving" > > > > <accessd at databaseadvisors.com> > > > > Sent: Monday, October 27, 2003 5:27 PM > > > > Subject: RE: [AccessD] Fw: [dba-OT] Keeping A Database Connection Open > > > > inTribble'sRelinker > > > > > > > > > > > > > Rocky, > > > > > > > > > > Here's what I did: > > > > > > > > > > ********* > > > > > On or about line 301 of ts_basTableLinks:: > > > > > > > > > > Private colRst As Collection 'collection to hold one > > open recordset > > > per > > > > > BE to speed up db > > > > > 'compiler directive for the logger > > > > > #Const LogIt = True > > > > > #Const TimeIt = False > > > > > > > > > > ********* > > > > > Then add in a new function below: > > > > > > > > > > > > > > > > > > > '----------------------------------------------------------------- > > --------- > > > > > ' Determines if a link to a table is valid by attempting to open the > > > > Table. > > > > > ' If this attempt fails the link must be invalid. > > > > > ' > > > > > ' Accepts: strTable = Name of Table > > > > > ' Returns: True if link is valid, False otherwise. > > > > > > > > > > > > > > '----------------------------------------------------------------- > > --------- > > > > > Private Function fLinkIsValidNew(db As DAO.Database, strTable As > > String) > > > > As > > > > > Boolean > > > > > Dim rst As DAO.Recordset > > > > > Dim var As Variant > > > > > > > > > > On Error Resume Next > > > > > Set rst = db.OpenRecordset(strTable) > > > > > If err <> 0 Then > > > > > fLinkIsValidNew = False > > > > > Else > > > > > fLinkIsValidNew = True > > > > > 'keep one recordset / BE open to improve performance > > > > > colRst.Add rst, db.Name > > > > > End If > > > > > On Error GoTo 0 > > > > > > > > > > End Function > > > > > > > > > > ********* > > > > > Notice that in this function I store a rst in colRst, with > > the BE name > > > as > > > > > the key. Doing this causes the collection to accept a single > > recordset, > > > > but > > > > > won't accept any others because the key would be the same. > > > > > > > > > > > > > > > ********* > > > > > On or about line 441 of ts_basTableLinks: > > > > > > > > > > LogProcess "Validating: " & mtbl.Name & " " > > > > > #Const TestNew = True > > > > > #If TestNew Then > > > > > If fLinkIsValidNew(mdb, mtbl.Name) And fForceRelink = False > > > Then > > > > > #Else > > > > > If fLinkIsValid(mtbl.Name) And fForceRelink = False Then > > > > > #End If > > > > > 'LogResult "[OK]" > > > > > '.If fCheckAll parameter is false, then quit after first > > > > > '.valid table link is found. > > > > > If Not (fCheckAllLinks = True) Then > > > > > > > > > > ********* > > > > > Notice that I use conditional compilation to call the new function > > > instead > > > > > of the old. > > > > > > > > > > That should do it for you. Let me know. > > > > > > > > > > John W. Colby > > > > > www.colbyconsulting.com > > > > > > > > > > -----Original Message----- > > > > > From: accessd-bounces at databaseadvisors.com > > > > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky > > Smolin - > > > > > Beach Access Software > > > > > Sent: Monday, October 27, 2003 7:52 PM > > > > > To: AccessD at databaseadvisors.com > > > > > Subject: [AccessD] Fw: [dba-OT] Keeping A Database > > Connection Open in > > > > > Tribble'sRelinker > > > > > > > > > > > > > > > > > > > > Dear List: > > > > > > > > > > I'm using Tribble's relinker in an A2K mdb. Does anybody > > know how to > > > > modify > > > > > this to keep a reference open to the back end so that it relinks > > faster > > > > over > > > > > a network? > > > > > > > > > > MTIA and regards, > > > > > > > > > > Rocky Smolin > > > > > Beach Access Software > > > > > > > > > > _______________________________________________ > > > > > AccessD mailing list > > > > > AccessD at databaseadvisors.com > > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > AccessD mailing list > > > > > AccessD at databaseadvisors.com > > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > _______________________________________________ > > > > AccessD mailing list > > > > AccessD at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > > > > _______________________________________________ > > > > AccessD mailing list > > > > AccessD at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > > _______________________________________________ > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > _______________________________________________ > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >