John Colby
jcolby at colbyconsulting.com
Tue Oct 28 10:15:23 CST 2003
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