[AccessD] Bind forms to ADO recordset

Yep, that's the way I've done it for years too.

Charlotte Foust

On Wed, Mar 7, 2012 at 7:18 PM, Darryl Collins <
darryl at whittleconsulting.com.au> 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
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>