<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=036424215-06062003><FONT face=Arial color=#0000ff size=2>Thank
you</FONT></SPAN></DIV>
<DIV><SPAN class=036424215-06062003><FONT face=Arial color=#0000ff size=2>Jim
Hale</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Charlotte Foust
[mailto:cfoust@infostatsystems.com]<BR><B>Sent:</B> Friday, June 06, 2003 9:49
AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE: [AccessD]
Fast back-end relinking?<BR><BR></FONT></DIV>
<DIV><SPAN class=542394714-06062003><FONT face="Comic Sans MS" color=#0000ff
size=2>For each table, open it in design view, select its properties and set
the Subdatasheet Name property to [None]. There is code in on-line help
to loop through the database and turn off all subdatasheets and code has been
posted here before and is in the archives.</FONT></SPAN></DIV>
<DIV><SPAN class=542394714-06062003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=542394714-06062003><FONT face="Comic Sans MS" color=#0000ff
size=2>Charlotte Foust</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Hale, Jim
[mailto:jim.hale@fleetpride.com] <BR><B>Sent:</B> Friday, June 06, 2003 6:08
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE:
[AccessD] Fast back-end relinking?<BR><BR></FONT></DIV>
<P><FONT size=2><turn off subdatasheets></FONT> <BR><FONT size=2>How
do you do this?</FONT> <BR><FONT size=2>Jim Hale</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: Jim
Dettman [<A
href="mailto:jimdettman@earthlink.net">mailto:jimdettman@earthlink.net</A>]</FONT>
<BR><FONT size=2>Sent: Thursday, June 05, 2003 2:28 PM</FONT> <BR><FONT
size=2>To: accessd@databaseadvisors.com</FONT> <BR><FONT size=2>Subject: RE:
[AccessD] Fast back-end relinking?</FONT> </P><BR>
<P><FONT size=2>Christopher,</FONT> </P>
<P><FONT size=2> The problem is not in the code itself, but what
goes on to make it all</FONT> <BR><FONT size=2>happen. You can really
speed things up by keeping a link open to the BE</FONT> <BR><FONT
size=2>(just opening the MDB in code is enough) before trying to relink the
tables.</FONT> <BR><FONT size=2>In fact, this is a good way to speed up a
split app in general.</FONT> </P>
<P><FONT size=2> When all references go out of scope for a BE, the MDB
is closed. When you</FONT> <BR><FONT size=2>go to use it again, it's
re-opened. This involves a considerable amount of</FONT> <BR><FONT
size=2>overhead and when the BE is running under NT (which has a poor
performing</FONT> <BR><FONT size=2>lock manager), the time can be
substantial.</FONT> </P>
<P><FONT size=2> Also make sure anti-virus scanning is turned off for
MDB's on both client</FONT> <BR><FONT size=2>and server. In Access,
turn off subdatasheets and autocorrect if your not</FONT> <BR><FONT
size=2>using them. And if the BE is running under NT/Win2K, look at
turning off</FONT> <BR><FONT size=2>Oplocks.</FONT> </P>
<P><FONT size=2>Jim Dettman</FONT> <BR><FONT size=2>President,</FONT>
<BR><FONT size=2>Online Computer Services of WNY, Inc.</FONT> <BR><FONT
size=2>(315) 699-3443</FONT> <BR><FONT
size=2>jimdettman@earthlink.net</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From:
accessd-bounces@databaseadvisors.com</FONT> <BR><FONT size=2>[<A
href="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>]On
Behalf Of Christopher</FONT> <BR><FONT size=2>Hawkins</FONT> <BR><FONT
size=2>Sent: Thursday, June 05, 2003 3:07 PM</FONT> <BR><FONT size=2>To:
accessd@databaseadvisors.com</FONT> <BR><FONT size=2>Subject: [AccessD] Fast
back-end relinking?</FONT> </P><BR>
<P><FONT size=2>First off: I have seen (and used) Tribble's Back-End
relinker</FONT> <BR><FONT size=2>before. That's not what I need in
this case.</FONT> </P>
<P><FONT size=2>I have inherited an app with some back-end relinking code
that is</FONT> <BR><FONT size=2>very slow. I've seen programmatic
implementations of a table link</FONT> <BR><FONT size=2>that linked up very
quickly, as quickly as using File > Get External</FONT> <BR><FONT
size=2>Data > Link Tables. The one I'm dealing with takes many
times as</FONT> <BR><FONT size=2>long.</FONT> </P>
<P><FONT size=2>There is a table in my db called tblTable. It contains
the name and</FONT> <BR><FONT size=2>path of every table in the
database. The table is used in the</FONT> <BR><FONT size=2>following
code:</FONT> </P>
<P><FONT size=2>***START***</FONT> </P>
<P><FONT size=2>Dim db As DAO.Database</FONT> <BR><FONT size=2>Dim tbl As
DAO.TableDef</FONT> <BR><FONT size=2>Set db = CurrentDb</FONT> <BR><FONT
size=2>Dim rsData As DAO.Recordset</FONT> </P><BR>
<P><FONT size=2> Set rsData = db.OpenRecordset("SELECT *
FROM tblTable")</FONT> </P>
<P><FONT size=2> rsData.MoveFirst</FONT> </P>
<P><FONT size=2> Do Until rsData.EOF = True</FONT>
<BR><FONT size=2> Set tbl =
db.CreateTableDef(rsData("TableName"))</FONT> <BR><FONT
size=2> Debug.Print "Now attaching
" & tbl.Name & "..."</FONT> <BR><FONT
size=2> tbl.Connect =
(";DATABASE=" & rsData("Path"))</FONT> <BR><FONT
size=2> tbl.SourceTableName =
rsData("TableName")</FONT> <BR><FONT
size=2> db.TableDefs.Append
tbl</FONT> <BR><FONT size=2>
rsData.MoveNext</FONT> <BR><FONT size=2> Loop</FONT> </P>
<P><FONT size=2>db.Close</FONT> </P>
<P><FONT size=2>***END***</FONT> </P>
<P><FONT size=2>All the lag takes place when running the
'db.TableDefs.append tbl'</FONT> <BR><FONT size=2>line.</FONT> </P>
<P><FONT size=2>Is there any way to speed this process up? I've seen
it done more</FONT> <BR><FONT size=2>quickly before, I just didn't get to
see the actual code. :(</FONT> </P>
<P><FONT size=2>-Christopher-</FONT> </P>
<P><FONT size=2>_______________________________________________</FONT>
<BR><FONT size=2>AccessD mailing list</FONT> <BR><FONT
size=2>AccessD@databaseadvisors.com</FONT> <BR><FONT size=2><A
href="http://databaseadvisors.com/mailman/listinfo/accessd"
target=_blank>http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT size=2>Website: <A href="http://www.databaseadvisors.com"
target=_blank>http://www.databaseadvisors.com</A></FONT> </P><BR>
<P><FONT size=2>_______________________________________________</FONT>
<BR><FONT size=2>AccessD mailing list</FONT> <BR><FONT
size=2>AccessD@databaseadvisors.com</FONT> <BR><FONT size=2><A
href="http://databaseadvisors.com/mailman/listinfo/accessd"
target=_blank>http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT size=2>Website: <A href="http://www.databaseadvisors.com"
target=_blank>http://www.databaseadvisors.com</A></FONT>
</P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>