[AccessD] [Accessd] Record Source for Reports Using SQL Db Backend - best practice

Borge,
I mainly use SQL as back end to Access these days. This is my approach:
1. Use an Access Project - connected to an SQL Server database using
"File/Connection"
2. Use Stored Procedures as Form and Report Recordsources
3. Filter the Forms/Reports by using the [Input Parameter] property - this
parameter is passed to the stored procedure that the form/report is based on
thereby giving you a perfect filter.

Example:
Form:                   frmCustomer
Stored Procedure: spCustomers is set as the recordsource
                            Procedure spCustomers (@CustomerID int)
                            as
                            Select * FROM Customers WHERE CustomerID =
@CustomerID

frmCustomer has the Input Parameter property set to =CurrCust()

CurrCust is a property I set before just before i open the Customer Form.
When frmCustomers open it will pass the input parameter CurrCust() to the
stored procedure and the form will be filtered to 1 customer only.

Hope this helps a bit.

Anita Smith








Anita Smith
On Mon, May 11, 2009 at 11:43 AM, Borge Hansen <pcs.accessd at gmail.com>wrote:

> 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
>