jwcolby
jwcolby at colbyconsulting.com
Mon May 11 06:25:20 CDT 2009
How long did Microsoft support projects? Through 2003? And what happens to a project when you hit the version where it is no longer supported? Does Access refuse to open it or does the SQL Server management stuff built in to the project just go away but the rest of the application continues to function? John W. Colby www.ColbyConsulting.com Anita Smith 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 >>