[AccessD] Fast back-end relinking?

Charlotte Foust cfoust at infostatsystems.com
Fri Jun 6 09:49:27 CDT 2003


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.
 
Charlotte Foust

	-----Original Message-----
	From: Hale, Jim [mailto:jim.hale at fleetpride.com] 
	Sent: Friday, June 06, 2003 6:08 AM
	To: 'accessd at databaseadvisors.com'
	Subject: RE: [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/3fbee2a0/attachment-0001.html>


More information about the AccessD mailing list