[AccessD] Fw: [dba-OT] Keeping A Database Connection Open inTribble'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
>