table locking?

[scottn]

Hi everybody,

I have built a very small split-DB app to test out the locking behavior of Access 2K under ADO. I have a backend DB that has only a single table. My frontend has one module with a procedure that opens an ADO recordset against the backend table, and attempts an edit to an existing record. I left the BE DB on the file server and put the FE on the local drives of two machines so that I could run it locally on both at once. Then I can step through the procedure on each machine to control execution timing and study the locking behavior.

The unexpected result, however, is that (in certain cases -- see below) as soon as one machine has a recordset open against the table, the other machine is unable to open a recordset against the same table. This is regardless of whether the first machine has begun any edits to the recordset. I didn't expect to see any conflicts in opening the recordsets -- I only expected conflicts when one machine had a record locked for editing and the other then tried to edit that same record.

Here's an important complication: one machine is running the full version of Access, but the other is running the Access Runtime. I only have this problem when the Full Version opens the recordset first, and then the Runtime tries to get a recordset open. It fails and raises this error: "Disk or network error" (#-2147467259). If the Runtime opens the recordset first, the Full Version seems to have no trouble opening a recordset and I can observe the expected behavior.

Does anybody know what's going on here? The implication is that if one of my users is editing a record, other users may be unable to even view data anywhere in the table. Does Access lock the entire table?

Thanks in advance for any help!

P.S. If you're wondering how I "step through" the code in the Runtime environment, I'm not using the VBE's debugging features -- I just put calls to MsgBox() between every line of code in the procedure.

 

[ReAn]

I use DAO, and from the looks of it, as long as no one physically opens the BACK END db, it has no issues.

 

[scottn]

ReAn,

Thanks for your reply. I modified my test app to use DAO and though I haven't tested as thouroughly yet, it seems that you're right -- I don't encounter this problem under DAO.

Does anybody have any further insight on this? Any ideas on why I'd encounter this behavior under ADO and not DAO? I built this test app to try to track down a problem in much larger app I built for our accounting dept. The real app is in production and includes a lot of ADO code -- I'd rather not convert it all to DAO if I can help it.

 

[scottn]

Further testing reveals the same problem whether locking optimistically or pessimistically under ADO. Also, I learned that the CurrentProject.Connection object does not support pessimistic locking -- you have to create a new connection to get pessimistic locking. Even after making this adjustment, I still can't open an ADO recordset from the machine running the Runtime environment once the other machine has one open. To tell the truth, given the error message I am not even sure this is a locking problem. Can anybody explain this?

 

[ReAn]

Sorry to be unhelpful but summary:

ADODB = t3h suck

 

[scottn]

ReAn,

Haha, evidently so!

It kind of surprises me since Litwin/Getz/Gilbert say pretty clearly in the Access 2K Dev. Handbook (which I thought was the authoritative book) that ADO is the preferred method going forward. I haven't worked with versions of Access past 2K... is ADO in 2003 or has Microsoft changed everything again?

Well, thanks for your responses!