[AccessD] Fast back-end relinking?

<turn off subdatasheets>
How do you do this?
Jim Hale

-----Original Message-----
From: Jim Dettman [mailto:jimdettman at earthlink.net]
Sent: Thursday, June 05, 2003 2:28 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Fast back-end relinking?


Christopher,

  The problem is not in the code itself,  but what goes on to make it all
happen.  You can really speed things up by keeping a link open to the BE
(just opening the MDB in code is enough) before trying to relink the tables.
In fact, this is a good way to speed up a split app in general.

  When all references go out of scope for a BE, the MDB is closed.  When you
go to use it again, it's re-opened.  This involves a considerable amount of
overhead and when the BE is running under NT (which has a poor performing
lock manager), the time can be substantial.

  Also make sure anti-virus scanning is turned off for MDB's on both client
and server.  In Access, turn off subdatasheets and autocorrect if your not
using them.  And if the BE is running under NT/Win2K, look at turning off
Oplocks.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
Hawkins
Sent: Thursday, June 05, 2003 3:07 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Fast back-end relinking?


First off:  I have seen (and used) Tribble's Back-End relinker
before.  That's not what I need in this case.

I have inherited an app with some back-end relinking code that is
very slow.  I've seen programmatic implementations of a table link
that linked up very quickly, as quickly as using File > Get External
Data > Link Tables.  The one I'm dealing with takes many times as
long.

There is a table in my db called tblTable.  It contains the name and
path of every table in the database.  The table is used in the
following code:

***START***

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
Dim rsData As DAO.Recordset


    Set rsData = db.OpenRecordset("SELECT * FROM tblTable")

    rsData.MoveFirst

    Do Until rsData.EOF = True
        Set tbl = db.CreateTableDef(rsData("TableName"))
        Debug.Print "Now attaching " & tbl.Name & "..."
        tbl.Connect = (";DATABASE=" & rsData("Path"))
        tbl.SourceTableName = rsData("TableName")
        db.TableDefs.Append tbl
        rsData.MoveNext
    Loop

db.Close

***END***

All the lag takes place when running the 'db.TableDefs.append tbl'
line.

Is there any way to speed this process up?  I've seen it done more
quickly before, I just didn't get to see the actual code.  :(

-Christopher-

_______________________________________________
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030606/422342ab/attachment-0001.html>