Problem with record-level locking: more than one records locked! 2003 1

[LongTLam]

I ran into this wierd situation.

I have this simple Access (2003) mdb setup:

1. 4 fields, incl number, text only, no OLE, no MEMO
2. No indexed fields
3. A simple form to show the fields
4. Form and Access' adv option both set to Record-level locking

What is strange is that if I commit editting one record at the form, all the records in the table are locked as revealed by opening the table from Access' interface.

Isnt Record-level locking supposed to locked just *1* record? How come this looks like page-level locking?

I have the db uploaded to:

http://www.mediafire.com/?u713wxa3d4hab53

Could someone please have a look and see whats going on?

Thanks.

 

[Hap007]

My understanding is that Access Locks blocks, not records.

So, I have heard that what you speak of is possible, I have never experienced it before.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[LongTLam]

Thats true only before Access 2003. For 2003+, record-level locking was introduced into JET 4.0

 

[jsteph]

Look in Help-->System Info, and see if you have JET 4.0, which is needed for this. 3.5 won't do record level locking in Access 2003
--Jim

 

[LongTLam]

I am using Access 2003. Should I assume the JET version is 4.0 by default (patch level may differ of cos) as discussed here:

http://www.utteraccess.com/forum/version-Jet-Engine-Acce-t352493.html

Jim could you please DL the MDB and see if you have the locking issue too? TIA!

 

[jsteph]

LongTlam,
I tested it in a database I have here and I get the same issue you do.

I've never noticed it before because I never use Jet tables in shared Access projects--I only use them for local client-specific scratch tables where locking is never an issue.

But your post made me curious so I tested in Access 2003 and got that (incorrect) behaviour, and my system info tab appears to say Jet 3.51, but it's really unclear. (this is in Help-->About-->System Info-->Office 2003 apps-->Access 2003-->Jet Core Compoents)

I tried to install the Jet 4 sp8 file, and it said I couldn't because it's already integrated with the OS (Windows XP sp2). So I really have no way of knowing for sure what Jet version is there--and numerous google searches don't really tell me how to verify it--the MSJet40.dll file that some posts suggest is the way to tell the version--that file is there, and registered, but I don't really know if Access 2003 is using it.

An MS article I found verifed that jet 4 should absolutely do Record-Level and not Page-Level locking if the Access engine options specify Shared use and if the first instance of access to open has Record-Level locking set. I closed all intances of Access 2003 on my machine, made sure all those options were set, and opened my test db, and it was indeed ignoring record-level locking.

However, the Page size didn't seem to change--it was locking 42 records when I had a table with 5 fields, all populated. I then added a 255 text field and copied every record with 255 dummy characters for that field. It still locked 42 records--I would have expected it to lock significantly fewer records had it been a standard 2 or 4k page.
So it's still a mystery.
--Jim

 

[Hap007]

Here is a Microsoft link explaining how to update Jet 4.0 on older versions of Access.

http://support.microsoft.com/kb/239114

From what I read, Access 2003 does not handle it automatically

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[PHV]

What is the value of CurrentDB.Version ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jsteph]

In my case the version is 4.0
However, I get the same locking problem as the original poster--If one record is under edit in a form or query, then it will lock (in my test case) 42 records when the table is opened in another query, form, or datasheet.

I looked at the link that happ007 posted, but that is saying my version is newer than than the update in that link.

So on the face of it, it's seeming like it's doing page-level locking even though all the criteria for doing record-level locking seems to be satisfied. I've only tested this via the user interface, ie, not using ADO and not using separate intances of the .mdb on different machines, and using a sole .mdb--no frontend/backend.

But one would think that if it's locking records from the current user from his own instance, that it would probably also lock those same from another user in another instance.
--Jim

 

[Hap007]

Hi Jim,

I just lloked at one of my development PC
On that PC, I have Access 2000, 2002, 2003 and 2007

When I select System info in any of those Access versions,
It states that my Jet is 3.5

I only mention this, because there must be something else that needs to be set, even in Access 2007 to use Jet 4.0

Or, maybe running multiple versions of Access cause this

Or else, my system info is not correct.

Very interesting indeed.

Have a Good weekend,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[LongTLam]

Nice to see this thread is getting some attention at last.

It is 4AM over here and let me appologize first that I had the solution a while back but for the sake of sanity I cannot believe it takes such a twist, shown below, to get the correct behavior of recording-level locking to function in Access 2003 or above!

Like Jim and Hap I did a search, and it landed me on a blog and finally to a microsoft link:

http://accessblog.net/2009/12/error-3218-c...-currently.html

http://support.microsoft.com/?id=306435

(PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60)

The essence of this matter is that, if a mdb file is touched (i.e. open) by DAO first, then vala! page-locking will kick in. So the trick is to first "touch" the database with ADO.

Sound wierd? Cos, think of it this way. Access form natively use DAO, so whatever database you create with a form will automatically be page-locked if you use bound fields.

I have made a FE and BE pair to illustrate this point. Following the code used in the above MS link, I now can achieve record-level locking in 2003. The trick is to open an ADO connection to the BE first before anything happens.

However this also means that the db MUST be split into FE and BE, cos how can one touch the db first with ADO if the FE and BE are the same file? (correct me if I am wrong)

You can view my same post in other forum on how I unraveled (but still stumped on other issues) the issue here:

http://www.utteraccess.com/forum/Problem-record-level-loc-t1948915.html

I am just amazed at how come this record-locking takes so much effort. I will continue to post in other forum (as a bait, hehe) to remind other ppl.

Enjoy!

 

[jsteph]

Longtam,
Excellent find. That is interesting and should be very useful for people looking for a solution to this.

It's so odd that MS was not more open about this--to essentially hide this info in an obscure msdn article--especially for something so core to the (then) new db engine--is dissapointing.

But I tested it in many different ways and that does indeed do the trick.

I'm still curious about page size though:
When page locking is in effect and I access the tables via directly opening the actual backend .mdb--it always, regardless of the data size of the records in the table--it always locks 42 records. Maybe the Hitch-hikers Guide was there reason, I don't know.

But when I do page locking via front-end, it appears as expected--the larger the record size, the fewer records are locked at a time.
--Jim

 

[LongTLam]

Jim,

Glad I can help. Credits go to the blog I posted above, and a colleague of mine who pointed out to me that there was locking issue in one Access app I rolled out.

I am curious about the page size issue too.

In small database with a few fields I tested with, in all cases the whole table was locked (100 records).

In a real world database with lots of fields (including tons of Memo fields and an OLE field), however, only the 4 adjacent records were locked.

Originally I was thinking the locking of >1 records was due to the presence of Memo/OLE fields, as pointed out by MS themselves. However, later I learnt that these pointer fields were not the culprit. Moreover, in the sample database I shown here. Single record locking can be achieved too even I have OLE/Memo fields (but then I have them in another table with 1:1 relationship). This could be the only good news about the whole event.

Keep me posted about ur latest findings.

TIA.