Anita Smith
anitatiedemann at gmail.com
Mon May 11 00:57:54 CDT 2009
Borge, The reason I went with this approach is that the SQL database becomes a complete unit containing all the functionality (Views and Stored Procedures) within itself and can be connected to a .Net project at a later stage if needed. Besides that - I really like stored procedures - they can do all the hard work for you. I also don't like the ODBC approach as I think it will impact on the performance. There is absolutely no difference between working in adp or mdb once the connection to the database has been set. It really is easy to make the switch to a project. If you don't want to bind the forms and reports to stored procedures you can actually work with the form recordsources as if it was an Access back end. You won't even know you are working with an SQL database. Trust me. Try it! Create a new project and select your SQL Database to connect to. Then create a form using the wizard. Anita Smith On Mon, May 11, 2009 at 3:08 PM, Borge Hansen <pcs.accessd at gmail.com> wrote: > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >