Error 3197: record lock errors

[dalebeitz]

Greetings!

I'm working on an all-Access 2002 application (no SQL Server or anything like that). Occasionally I'll get the following error when Access tries to save my data: "The Microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time". Responses are OK and Help. If I go to help, it tells me this is error 3197. The problem only happens sporadically, so I can't nail down a specific circumstance that will always cause it to happen.

I can guarantee that I'm the only user in the database at the time, since this is my development copy where it's happening, and it's stored on my personal network drive where no one else has rights (except other admins, and I know they're not messing with my files). The production version of the app only has two authorized users who can get into it, and they've gotten the same messsage on occasion when one of the users was physically out of the office. So I'm positive this has occurred when only one user was in the database.

I saw thread 701-122269 where somebody had a database on their hard disk (again guaranteeing single user circumstances) and was getting the same error, but it didn't help me. The reply referenced another thread, 181-122291 that talked about the problem being related to using certain data types with SQL Server as a back end. So I don't think that applies to me.

About the only other thing I can think of is that I am linking to tables in other Access databases. Our main business management app is written in Access, and that's where the customer master, product master, etc. all live. My application links in the customer master, but nothing in my application modifies the master tables. Our policy is that no bolt-on app is allowed to change data in the master files, that's only allowed through the business management app. There is no way to create a read-only database link that I've found, so it's possible that a developer in datasheet view in my app could change one of the master tables. But the problem is occuring when I'm using my forms, which don't provide any way to edit the master tables. Just in case there was some conflict with the linked tables, we created shortcuts for all the master databases and linked through the shortcuts, which one of my colleagues tells me can help solve multi-user Access database issues. But the problem still happens on occasion.

Any ideas greatly appreciated.
Dale Beitz
dale.beitz@agreliantgenetics.com

 

[cmmrfrds]

Here is a link that gives a little overview on locking.

http://www.vbtt.com/articles/multi_user_locking_methods_in_vb.html

Since you are the only one in the database, you may be blocking yourself. For example, if you have the same recordset open in 2 different forms that are both using a dynaset or similiar locking recordset that could cause it. Examine the control flow in your program to see if that may be happening. Another possibility is a combobox to select a record that will be open on another or the same form. Also, check that you are not doing pessimistic locking on the recordsets. Make sure you tab off any record so the update is committed before opening the same table again.

 

[dalebeitz]

Thanks for the ideas!
I'm not sure about the users, but I know in my testing I never have more than one form open at a time, and each table only has one form that allows editing. So I'm sure I'm not locking with myself that way.
According to help/about, I'm using Access 2002 (10.4302.4219) SP-2. If I go to Tools/Options and select the Advanced tab, the right half of the dialog deals with record access. At the very bottom is a checkbox that reads "open databases using record level locking". F1 on that field says "Select to make record level locking the default for the current database. Clear to make page level locking the default". And I do have that checked. So unless there's something different going on at the JET engine level than is implied here, I'm using record level locking.
Above that is an area for default record locking. Choices are no locks, all records, and edited records. Since my users are doing heads-down data entry from paper forms, and I only have two users, they should never be editing the same record. The only chance this could occur is if they went back to correct a data entry error, and even then only one of them would be correcting a given error at a time, so I shouldn't have two users in the same record. So I've set this option to "no locks", which should give me the least problems in terms of multi-user access.
So everthing I can find about record locking tells me I shouldn't be having problems, unless JET truly doesn't do record level locking contrary to what the help says, but instead does page level locking.

The one thing that might apply is the mention of combo boxes. The customer master table is part of our business management app, and I access it via a database link. My data entry form has a combo box on it that pulls the customer name and key value from the linked customer master via a query and then populates my table with the key value. It is possible that somebody in the business management app could be editing or somehow locking the customer record or page of customer records that I'm referencing via my combo box. But again, my forms provide no way to edit data in the customer master, so I figured I was safe. Is it possible that if someone changed data in the customer master it would cause my form to complain about a record lock?
Thanks!
Dale

 

[cmmrfrds]

Is there a way you can isolate the linked master table. For example, temporarily create the table in your Jet database even if just a subset of the records and point your Form to it instead of the linked table. That would at least tell you if it is the table causing the problem and you could zero in on it, or rule it out.

 

[dalebeitz]

It would be possible to make a local copy of the customer master, but not practical. My app is a salesman activity tracking database. The salesman are constantly calling on new customers, and the new customers are all added to the customer master through the business management app. If I were to make a local copy of the customer master, then my users couldn't do data entry for newly created customers. And since I can't reliably reproduce the problem, it could take quite some time to determine if the local copy solved anything or not. I'll try that in my development app and see if it gets me anywhere.
Thanks again!
Dale

 

[cmmrfrds]

Maybe it is the business management app that is locking the record longer than necessary, larger than record level, or more restrictive lock options. Can you set up a test to be in both apps and try different scenarios of adding or changing customer data in the business management app.

 

[dalebeitz]

That's a very good idea - I should have thought of it! The team that's responsible for the business app has a test version set up for training, etc. What I could do is set up a time with them when I can have exclusive access to the test data, then link to the test version of the customer master and be in both apps at once.
Thanks again!
Dale