[AccessD] Fw: [dba-OT] Keeping A Database Connection OpeninTribble'sRelinker

As John said, this makes a difference when there are multiple users in the
database.  If you have a small network, try it with another copy of the same BE
database opened on a different computer (open a form, start an edit, etc)

Stuart

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Rocky Smolin - Beach Access Software
> Sent: Tuesday, 28 October, 2003 11:59 PM
> 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
>