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?

Terry

I'm searching documentation on ACEDAO too :S – Slew

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.

Phelps

Great detail, thanks for the extra effort. Any idea where I could find out either way about the whole ACEDAO row level locking thing? – Terry

Albert, can you offer citations on this? It's certainly news to me that turning on row-level locking causes Jet to store one record per data page. – Desulphurize

Thanks David, that's exactly what I'm looking for here: citations, articles, Help files, white papers, etc. – Terry

Thanks, I'll a take a look soon -- I'm busy too ;) P.S. in future can you can you omit your sig from your answers as per faq: "Signatures and fluff are discouraged and are fair game to be removed." (#15093) I don't mind doing it, I might get a Blue Peter badge or something :) – Terry

The link you gave about signatures is broken. Furthermore the issues is taglines in a signature, or links in a signature, or a message in the signature. A signature of it own is not really a big issue here. You can continue to edit them, but you are the only one doing so. The real rule in the FAQ is to respect the authors wishes. If you can find an faq, or link that suggests a signature (without a tagline or link) is not in the sprit of the StackOvetflow, then I will certainly abide by those rules and extend that curtsey to everyone here. I believe the issue is taglines in the signature. – Phelps

I've seen posts about it but I think they've now been moved to the meat site (probably why links are broken). The faq (stackoverflow.com/faq) says, "this site is collaboratively edited. If you are not comfortable with the idea of your questions and answers being edited by other trusted users, this may not be the site for you" This topic (#15093) says, "Signatures and fluff are discouraged and are fair game to be removed." – Terry

I'm not the only one doing this e.g. David W. Fenton in this thread at this link: stackoverflow.com/revisions/75061/list – Terry

This from Jeff Atwood (stackoverflow co-founder): "Please use your profile, avatar, and username as your signature; that's what they are there for! If this is a concern, perhaps Stack Overflow isn't the right website for you." (meta.stackexchange.com/questions/5029/…) – Terry

Answer 2

ACEDAO at least and most likely DAO uses record-level locking by default, irrespective of the MS documentation. This can be evidenced by the bloat (mentioned above) that occurs after switching from an underlying Access 97 DB to an Access 2000 or ACCDB, using the same DAO code.

For further details, see my answer here:

Is it better to use ADO or DAO in Access 2007?

Edited to reflect an MSDN link containing the correct info:

"Record-level locking is the default for ADO and DAO Recordset objects. Page-level locking is the default for SQL DML statements (bulk operations such as UPDATE, DELETE, and INSERT INTO statements) that use ADO Command objects or DAO QueryDef objects."

http://msdn.microsoft.com/en-us/library/aa165435(office.10).aspx

Heteroplasty

I think that MSDN article is mistaken. The places where it says or implies that DAO can use record-level locking are rather unclear, and, indeed, the only code example that uses record-level locking is ADO and not DAO. Indeed, if I look in the A2003 help file, there is no option available there at all to open a recordset with record-level locking, and when I check the same help in A2007, I see no options that can be used to force opening the recordset with either type of locking. There is no code in that article that shows how to open a DAO recordset with record-level (or page-level) locking. – Desulphurize

Can you provide DAO code that allows me to open a recordset and choose the locking type, i.e., record vs. page locking? I don't think it can be done, despite what that MSDN article says (and keep in mind it's for A2000, which means it was likely written c. 1999, at a time when maybe they still intended to implement that in DAO 3.6). – Desulphurize

@aristippus303: I'm not convinced by your assertions about row level locking being default and don't see how the existence of bloat can be used as evidence. You have to look at the underlying reason for the bloat and Albert D. Kallal's explanation sounds compelling but similarly lacks evidence. Ah yes, when they made Access they threw away the mold... and seemingly destroyed all the good documentation too. – Terry

@David-W-Fenton: ...and I have read all your links too and I don't see that it adds up to much. However, I do thank you for bringing to my attention the article "Manipulating Objects with DAO May Cause Database Bloat" (support.microsoft.com/default.aspx?scid=197953) where the recommendation from MS is to "Use SQL Data Definition Language (DDL) statements rather than DAO to create or modify database objects." This could be a life changing moment for @Desulphurize ;) – Terry

David, there is no way to choose the locking type in DAO. As I mention in my other post, you need to use ADO/OLE DB to do that. – Heteroplasty

We recently ported rock solid DAO code using Jet 3.51 "hosted" in Jet 4.0 (we were using an A97 DB) to ACEDAO (for use with ACE 12 x86 and ACE 14 x86 & x64). The additional evidence is that in addition to the bloat, the only way to avoid the lock errors that now appeared was to increase the MaxLocksPerFile to a huge value. Once we implemented the "connect first with ADO to set the locking strategy, then do the heavy lifting with DAO" trick, no locking issues. I assure you we are well versed in DAO/Jet. This is the app. datawatch.com/_products/monarch_pro.php – Heteroplasty

@aristippus303: so is your recommendation to use page-level locking with ACEDAO? – Chalutz

@aristippus303: does it make sense that you would get more lock errors with row-level locking? I would have thought it would be the opposite. – Chalutz