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

Borge Hansen pcs.accessd at gmail.com
Mon May 11 00:08:49 CDT 2009


Anita,
Your comments were helpful, thanks.
I understand your approach is based on Access Data Project (.adp ) file ....
I have never developed using this file type.. I think the general advice
from back in the Access2000 days to stay with .mdb ....
I created my first .adp and had a look ....

Our approach so far has been using .mdb as we either are converting exsting
apps to SQL Db backend or so firmly anchored in the .mdb approach that also
a new App would be based on a mix of ODBC linked sql tables and Stored
Procedures using ADO.....

Has anyone a link to or list of information about what is lost / gained from
going from .mdb to .adp approach?
Pros and Cons for each approach??

For example we make use of linked temporary tables held in a separate local
.mdb stored in each user's application folder for some functionality.
I think we lose the ability to link to access tables in an .mdb file from an
.adp application....

borge

On Mon, May 11, 2009 at 1:46 PM, Anita Smith <anitatiedemann at gmail.com>wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list