Jim Lawrence
accessd at shaw.ca
Mon May 11 00:05:15 CDT 2009
You can always try the technique of using ADO-OLE to acquire data directly from the SQL BE and dumping the contents into a temporary DB built to feed a report...very fast and no filters or ODBC. Check out link: http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo rts.asp The demo is bit dated as it is built around MS Access 2000 and MS SQL 2000 but the basics have not changed. (Could process 50,000 plus records in 1.7 seconds on a 60+ user application.) HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Borge Hansen Sent: Sunday, May 10, 2009 6:43 PM To: Access Developers discussion and problem solving Subject: [AccessD] [Accessd] Record Source for Reports Using SQL Db Backend - best practice Hi All, The last year and a half or so I have learnt more and more about SQL Db and working with Access FE against SQL BE. I've started using ADO recordset returned from SQL as record source for combo boxes, list boxes and view forms. Recently I started re-working some Reports on an existing application made to run against SQL db that I've taken over maintenance for. These reports run sort of OK as they are - using existing Access queries against ODBC linked tables. But: For example one report use as record source an SQL string on one table with no criteria filter. The filter is placed on opening of the report, like this example: DoCmd.OpenReport "rptAuditRollBook", acViewPreview, , "[ChildID]='" & Me.subfrmChildDetails.Form.ChildID & "'" Is that a good practice - in regards to multi user setup and network traffic? I've read somewhere that Access in retrieving query results not necessarily brings across from the network store all of the columns in the table(s) but only the indexes it requires in order filter out the query result, which is then brought across the network to the FrontEnd. With a docmd statement as the above, does it work as if the filter was part of the report record source SQL Access query string - or does the report open, then pull in all records based on the Access query string, then filters the query result before displaying ??? If it's the latter, wouldn't it be better to build the record source query on the fly - so that only the subset of records are brought across the network and displayed ?? Further, how can we best utilise the SQL Db to provide only subset of data for reports? I've tried to use as record source for a report an ADO record set created by the SQL Db and served to the Frontend - but that doesn't appear to work for reports. Have I missed something here, or can you people confirm that this is the case: as record source for reports with SQL Db as backend we can only use ODBC linked tables and access queries based on ODBC linked tables ??? regards borge -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com