[AccessD] Bind forms to ADO recordset

Well the same thing goes for bound forms apparently!

And yep, that is what I am talking about.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 3/7/2012 10:18 PM, Darryl Collins wrote:
> Heh... You are probably not going to like this, but I have always done this unbound forms.  So when the user clicks on the primary form a SQL command is then sent to get just the data into the subform that is relevant for the selected record only, and nothing else, from SQL Server. This usually means only a tiny percentage out of the total record set needs to be sent over the wire.
>
> Something like "SELECT * FROM TblXYZ WHERE pkMyKey  = pkUserChoice ORDER BY myChosenSequence"
>
> Does that help, or am I way off track here John?
>
> Cheers
> Darryl
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, 8 March 2012 1: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
>
>
>