Table locks and views

  • 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!

  • 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.

  • 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

  • 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".

     

  • 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!

  • 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