ms access - Does ACEDAO support row level locking?

Question

There was a problem with DAO 3.6 in that it didn't support row-level locking. For details, see this Knowledge Base article.

I learned from the Access team's blog:

In Office Access 2007, new objects, properties, and methods will be added to DAO to support the new features in the Access database engine.

From looking at this library in the Access2007 Visual Basic Editor's Object Browser that the new incarnation is called ACEDAO. I can find mention of acedao.dll in hotfixes on MSDN but nothing else.

Does anyone know where the end user documentation for ACEDAO can be found?

How can I discover whether ACEDAO supports row-level locking?

Answer 1

I don’t believe there’s been any change in that knowledge base article. However you have to keep in mind how record locking was implemented.

Remember that when any table by any user on the network is opened, and they have row locking enabled, then it will not matter how DAO/ADO/ACE opens that table in code.

Any existing users with row locking will force row locking for THAT record they are using. And, since it is very rare that that recordset code is going to keep a record open/locked for any reasonable amount of time then page locking or row locking is rarely going to make a difference here. So in most cases locking is needed when you have a user editing data in a form. In the case of code, it tends to be a rare problem.

So the user settings in tools->options really determines this choice for the user. To be really clear here and not split hairs, that row locking setting for the UI doesn’t really override the choice, as much as by default it FORCES the issue.

Remember the way row locking works when you turn that feature on, is the jet engine simply expands the size of the record to that of one page, and therefore just uses a plain old fashioned page locking like it did before. This is what I mean by forces the issue. It not going to make a hill of beans different if the user has Turned on row locking, and then you try and grab a record that’s locked by a user with a recordset that did not open the reocrdsset with row locking (as opposed to page locking). You not be allowed to lock and edit the record because this comes down to the page resolution again.

There’s no such thing as a true row lock. However, if you expand the size of a record to always one page, then you accomplish the same goal in a roundabout way.

Given the above information, this means two things:

First of all it’s not going to make any difference if you open your reocdset as page or row if a user already has the record locked with a form. The user editing the data will determine how much of the page are going to be using up (and it is a full page of data if row locking is turned on).

2nd issue. You want to think long and hard about using this option, because it causes files to bloat very rapid since each record edited will be forced to take up one database page. If you’re running code that updates thousands of records, then you likely don’t wanna do this. However user cannot really edit that many records in a day, and with a daily compacting routine, it becomes not an issue again.

Logically, this means that the option is really only useful for users editing data WHEN they are tripping over each other. However for recordset code it’s rarely going to help or even be appropriate to use row locking.

At the end of the day, the feature really is only appropriate if you have several users that are trying to get to the same data in the same form. So, this is more of a user interface feature then that of using code + reocordsets.

You might find out that ACE + dao can open a reocrdset with row locking, but why worry? YOu don't use dao, and you don't like it anyway. And, it not likey going to change your user scenarios anyway.

-- edit:

I don’t have a link handy that explains this record page locking. I know I read this somewhere, but I don’t’ have a link.

I been really busy for the last few days (I am part of the access 2010 beta, and on Monday there is also the CTP released to the public people who signed up). So, I been realy busy of late.

However, you can try some code that will show this. The following code causes about 1 meg of bloat in a mdb with NO row locking tuned on. (it goes from abou t 5.8 megs to about 6.5).

Dim rst        As DAO.Recordset
Dim i          As Long
Dim t          As Single

Set rst = CurrentDb.OpenRecordset("contacts")

t = Timer
Me.Text1 = "working..."
DoEvents
Do While rst.EOF = False
   rst.Edit
   i = i + 1
   rst!City = "ci " & i
   rst.Update

   rst.MoveNext

   If i Mod 500 = 0 Then
      Me.Text1 = i
      DoEvents
   End If


Loop

t = Timer - t
Me.Text1 = "done in " & t & " seconds"
Beep

If run the SAME code with record locking on, the file becomes whopping 123 megs in size. Here is link to a sample mdb with the above code in it

http://www.kallal.ca/test/bloat.zip

So, download above. Run it with tools->options->advanced-> open with row level locking.

Then, empty the table. Compact and repair. Change the above setting and run it again (make sure you exit, and then re-enter the database after you change that setting).

If you run above without row locking..the file increases by about one meg. If you run it with row locking on, you get well over 110 megs of data file increase. It is a VERY large difference indeed.