David McAfee
davidmcafee at gmail.com
Thu Mar 8 12:42:07 CST 2012
Stored procedures can do this. (Warning Aircode:) me.sbfrmDiary.recordsource = "stpDiaryRecord " & me.parentID me.sbfrmExpenses.recordsource = "stpExpensesByParentID " & me.parentID On Thu, Mar 8, 2012 at 4:06 AM, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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@**databaseadvisors.com<accessd-bounces at databaseadvisors.com>[mailto: >> accessd-bounces@**databaseadvisors.com<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@**databaseadvisors.com<accessd-bounces at databaseadvisors.com> >> [mailto:accessd-bounces@**databaseadvisors.com<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<http://databaseadvisors.com/mailman/listinfo/accessd> >> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> >> >> >> >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> > Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> >