Page-level locking in Jet 4.0 insert? "Can't update; currently locked" error

I'm getting this error intermittently before record insert on a subform.

The subform is in 'continuous records' mode. The subform recordsource is a parameter query opened via querydef.OpenRecordset, in dbOpenDynaset mode.

Using MS Access 2003, Access 2000 file format for front-end and back-end, both FE and BE have record-level locking selected, all forms have "No Locks" for RecordLocks.

Googling, it seems many people have had similar situations but no solutions found.

Any ideas? It seems like page-level locking is being used instead of record-level, when the insert is done on the form.

1. Could it be that the back-end connection (via DAO OpenDatabase) reverts to page-level locking? Any setting in the DAO connectionstring that could be helpful if so?

2. Could it be that by opening the recordset via querydef, 开发者_如何学Cit uses page-level locking, so that a simultaneous record-update and record-insert (recordset.AddNew or whatever) may conflict? Microsoft's article here suggests this might be the case (although it's unclear, as OpenRecordset is supposed to be OK):

However, any SQL Data Manipulation Language (DML) queries — that is, queries that add, delete, or modify records — that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed — they still require page-level locking.

This is what I think the source of my problem is, and perhaps why Aaron said that Jet "randomly locks records". From the DAO docs:

Recordset.AddNew Method

Note When you use AddNew in a Microsoft Jet workspace and the database engine has to create a new page to hold the current record, page locking is pessimistic. If the new record fits in an existing page, page locking is optimistic.

I think I am seeing it more in this project because it involves frequently inserting a large number of records when a user opens a form, therefore the db engine is creating a lot of new pages, therefore AddNew - as called during the Insert event on the form - has more likelihood to revert to pessimistic locking, therefore more chance for collisions with other records being edited concurrently on the same page.

I will set up a testbed to see if my suspicions are true.

If so, it seems like you could avoid it by not using AddNew (AllowAdditions = False), and instead insert new records via a query + refresh the form recordset. Even if the insert query forces a new page, with querydef.Execute you aren't opening a recordset, so theoretically there should be no locking issue.