Anita Smith
anitatiedemann at gmail.com
Mon May 11 01:07:27 CDT 2009
Borge, Regarding linked tables for temporary data you can still use SQL Server tables for that. I also use that approach quite a lot. You will then have to add a user field or something so you don't overwrite or delete other users temporary data. I remember that it was my biggest hurdle when switching to a project, but I overcame it after I managed to develop a usable approach. 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 >