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

Rocky Smolin - Beach Access Software bchacc at san.rr.com
Wed Oct 29 08:51:57 CST 2003


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



More information about the AccessD mailing list