[AccessD] Fw: [dba-OT] KeepingADatabaseConnectionOpeninTribble'sRelinker

John Colby jcolby at colbyconsulting.com
Wed Oct 29 10:32:41 CST 2003


You know, I think there are options that you can set to do what you are
talking about.

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 11: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





More information about the AccessD mailing list