[AccessD] Bind forms to ADO recordset

Darryl Collins darryl at whittleconsulting.com.au
Wed Mar 7 23:35:04 CST 2012


One way of doing the pre-processing without any coding etc is to use views in SQL server and refer to them rather than the underlying table.

Say you have a very large dataset - use views to break it into logic segments that you commonly use (Say one set of views x Fin Year, Another set x State, or whatever).  That way you can your database behave nice and fast for the user.  For example if they are looking for all sales data in the date range of 1-March-2011 to 30-Jul-2011, you can then search only the view that has that range, rather than the whole darn table.  You can even break it down further to suit your pre-defined queries.  You can also get smart and use union to join up just the views you need if things wrap over.

Using this approach can speed things up a lot as the volume of data to search thru is already limited.

Cheers
Darryl

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, 8 March 2012 2:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Bind forms to ADO recordset

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list