Record-Level locking is locking 2 records 1

[KornGeek]

I'm using Access 2000, and in both the front-end and back-end, I have it set to open using record-level locking, with Edited Record as the default.

I am doing a library-type database, and I'm having a problem with my form where the users check out the books (frmCheckOut). This form has its RecordLocks property set to Edited Record. Sometimes, instead of locking only the currently edited record, it will lock a second record as well. It only happens if I follow a series of steps, so please bear with me.

I have a transactions table and a books table (tblTrans and tblBooks) that have a many-to-many relationship (using tblTransBooks). In frmCheckOut, I use record locking to secure the current transaction record. The user enters the transaction information, goes to a subform to select books (which I lock by setting a dummy field), and then completes the transaction (which actually updates the Book records). After completing the transaction, the form is still open to allow the user to review the transaction information and print a receipt.

The only way I have been able to reproduce this error is:
1) User 1 begins a transaction (Transaction #1) and selects a book (Book #1) as part of the transaction.
2) User 2 begins a transaction (Transaction #2) and attempts to make Book #1 part of the transaction. User 2 receives an error message.
3) User 1 completes Transaction #1, but does not close the form or move to another transaction.
4) User 2 attempts to add Book #1 to the transaction again, but receives an error because it is alredy checked out.

What happens now is that User1 now has Transaction #1 and Transaction #2 locked in tblTrans (even if they are not consecutive records). This prevents User 2 from completing Transaction #2.

This does not happen unless I get both error conditions. If User #1 closes the form, the lock disappears. I can see no reason why Access would choose to lock Transaction #2 for User #1. Please help (and thank you for your patience with this lengthy explanation).
Sleep is for people with no caffeine.

 

[KornGeek]

After doing some further testing, I have found that the bug report I received was missing some info. The errors that User 2 received are not necessary. I was able to reproduce this by simply having them both entering books at the same time, and then finalizing either transaction. By going directly into the table, I was able to determine that it is locking a handful of records. Is it possible that it is somehow doing page-level locking, even though record-level has been selected?
For good advice mixed with a little humor, check out

http://www.mvps.org/access/tencommandments.htm

 

[KornGeek]

After further poking around, I think I may have found the answer.

http://support.microsoft.com/default.aspx?scid=kb;en-us;238258

Apparantly, opening the database from a shortcut causes Access to use page-level locking. SR-1 is supposed to fix this. I will try that out.
For good advice mixed with a little humor, check out

http://www.mvps.org/access/tencommandments.htm

 

[chrissie1]

no thanks, we are glad to help ) Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[KornGeek]

Unfortunately, the service release did not fix the problem. I've even tried starting it without using the shortcut, but it's still doing page-level locking (approximately 60 out of 160 records are being locked).

Any suggestions, ideas, or even random ramblings would be appreciated.
For good advice mixed with a little humor, check out

http://www.mvps.org/access/tencommandments.htm

 

[KornGeek]

In

http://support.microsoft.com/default.aspx?scid=kb;en-us;306435

Microsoft says "However, row-level locking of an Access database is not available with Data Access Objects (DAO) 3.60. To resolve this problem, use ActiveX Data Objects (ADO) to enable row-level locking on an Access database, and then open DAO connections to the database. All subsequent attempts to open DAO connections to the database will respect the locking mode that you set."

How can I determine if Access is using DAO or ADO to establish the initial connection? In the VBA editor, I made sure that ADO is listed as a higher priority than DAO. I briefly use DAO in one of my subroutines, but I believe I am using ADO by default everywhere else.

Please, somebody help me confirm that this is or is not a potential problem.

Help!
For good advice mixed with a little humor, check out

http://www.mvps.org/access/tencommandments.htm

 

[KornGeek]

I think I finally figured this out. Unfortunately, I was inches away from the answer hours ago, but just missed it. After installing the service pack, I either didn't reboot the computer, or didn't shut it down completely. When I tested this on another computer, I tested to make sure it failed, installed the service pack, then completely shut down. When I rebooted, all was right with the world.

I'm still a little dazed, but I think I got it working. Now, I just hope I've got the process down. I think I'll have the testers continue to look for this with the next few revs. Anybody got some tequila? It's been a rough day.