jwcolby
jwcolby at colbyconsulting.com
Mon May 11 10:08:52 CDT 2009
This is full support including the ability to create and edit stored procedures etc? John W. Colby www.ColbyConsulting.com Martin Reid wrote: > 2007 supports then and I would say its a safe bet but not 100% that another version should. > > Martin > > > Martin WP Reid > Information Services > Queen's University > Riddel Hall > 185 Stranmillis Road > Belfast > BT9 5EE > Tel : 02890974465 > Email : mwp.reid at qub.ac.uk > ________________________________________ > From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] > Sent: 11 May 2009 12:25 > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] [Accessd] Record Source for Reports Using SQL Db Backend - best practice > > 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 >>> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com