Darryl Collins
darryl at whittleconsulting.com.au
Thu Mar 8 18:15:33 CST 2012
Yep, the segregated views approach will work better for some setups than others. I suspect your is one where it won't be so handy due to the nature of what you want to do. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, 8 March 2012 11:06 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Bind forms to ADO recordset I understand what you are talking about but this specific scenario is about the subset of child records that belong to the parent record. A claim form (parent) and all the diary records child records / subform) for that claim. All the expense records for that claim. All the memo records for that claim. All the payment records for that claim. Etc. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 3/8/2012 12:35 AM, Darryl Collins wrote: > 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 > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com