[AccessD] Bind forms to ADO recordset

jwcolby jwcolby at colbyconsulting.com
Wed Mar 7 20:51:22 CST 2012


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



More information about the AccessD mailing list