<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] Fast back-end relinking?</TITLE>
</HEAD>
<BODY>
<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>
</BODY>
</HTML>