September 17, 2006 at 9:15 pm
Not really sure what category to put this in, but here goes:
We have an Access application that uses ODBC to connect with a 2000 database. One of our built in Access reports is quite large (40+ subreports). When printing the reports, we use the linked tables. At the same time, people are processing large amounts of information - updating/inserting/deleting data in the same tables (but not the same records - just the same table). The reports are causing the tables to be locked - preventing processing.
My first thought is to put the data for the reports into views and have the reports link to these views. Would this help prevent the locking issue (not sure exactly how the views work in regards to this)?
Since these are ODBC linked tables, any other ideas as to how to minimize locks (some db maintenace perhaps)?
As a side note - we are beginning to transition all these reports into SQL reporting services, but it will take several years before the process is complete.
Thanks for any suggestions!
September 18, 2006 at 12:58 pm
Not sure if it may help. I've created a querydef that called a stored procedure. The access-reports recordsource would be then that querydef. Not sure if I've layered another querydef in order to make it work.
September 18, 2006 at 11:11 pm
I am not sure of the access application but, it is always a good idea to use proper Locking options in your queries and stored procedures to avoid locking and I believe most of the applications have similar activities of multiples inserts, updates and deletes taking place simultaneously. What kind of locks do you see on the tables, and what is the service pack running on your 2000 database.
Thx
Prasad Bhogadi
www.inforaise.com
September 19, 2006 at 5:49 am
Have you tried setting "Record Locks = No Locks" on the "Other" tab of the report design properties in Access. You really only need "read only" access to data showing on a report. If the reports are sources by Access queries - set the query's recordset property to "snapshot".
September 26, 2006 at 9:24 am
Sorry for all the responses - I've been on vacation and never turned on a computer. Perfect!
Per Jo Pattyn: I use stored procedures whenever possible. The problem resides in sub-reports using linked fields - cannot use stored procedures for the parent or sub-report (and I must link in order to do proper grouping).
Per Prasad: We have service pack 3 installed. As per the locks, I believe most of them are table locks (per the clients info). The majority of locking is taking place on a client and I'll have to look at their data. As I am not really a db person (really a .net or java developer), how should I locate the locks and determine the type?
Per Monte: I have the No Locks setting and it doesn't seem to make a difference. For many of the sub-reports, I am using a defined Access query and am not sure how you set it to be a snapshot for a report - any help there would be nice. I also set some of the recordsources for the reports at runtime using me.recordsource="Select ..." - again, can you define it as a snapshot?
Thanks for all the help!
September 26, 2006 at 9:57 am
Oops - I'm not sorry for all the responses, I'm sorry for my own late responses!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply