[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