Jim Lawrence
accessd at shaw.ca
Wed Mar 7 21:59:10 CST 2012
John: Do not bind, especially that many records...set your SP to pull only the records needed or pull the records in groups by record number grouping...say record 1 to 1000, 1001 to 2000 and so on. Pulling 700,000 records is insanity...that is what MS SQL was created for. Pre-process first. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, March 07, 2012 6:51 PM To: Access Developers discussion and problem solving Subject: [AccessD] Bind forms to ADO recordset It is my understanding that binding a form to an ADO recordset allows a SQL statement to be "passed through" to SQL Server for very efficient processing, for example for sorting or selecting on the server. My question is, how do I prevent up front inefficiency in subforms. We know that Access uses Link Child / Master field properties to "narrow down" a set of records from the entire table to just matching records. What happens if I bind the subform to an ADO recordset? I can tell you from testing it that it is slower than sin! It does appear to pull the entire table into the sub form, and then perform the filtering. I was using a sql statement basically "SELECT * FROM TblXYZ" which is of course unfiltered in any manner. The specific table I am trying to pull data from has about 700K records. Yikes! Is anyone out there binding subforms to ADO recordsets and if so what is the trick? The only way I am making it work is to filter the subform using the PK of the main form in a dynamic sql statement, and essentially rebuilding the recordset in OnCurrent of the main form. Hardly pretty. -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com