Error: 3218 Could not update; currently locked

My client got this error on very simple database, application was split into backend and frontend, BE was located on one of user’s machine (Windows XP) and accessed using network share. Error occurred quite randomly, there was no way to reproduce it. I had no idea who built it, personally I never got such errors in my applications. Fortunately Tom Wickerath helped me to resolve it (check also his article Implementing a Successful Multiuser Access/JET Application):

Are there any tables that include memo, hyperlink, OLE Object, or the new multi-value field (Access 2007 .accdb only)? The reason I ask is that these fields can cause page locking to be invoked, when you might otherwise assume that record locking is being used. For JET databases, I have gotten into the habit of breaking memo fields out to a separate table, with a 1:1 relationship, instead of including the memo field in the same table with other fields. This way, when a user clicks into the memo field on a form, they have immediately committed any changes to the parent record and vice-versa, clicking out of the memo field on the form to any other field commits changes to the memo data. I base this on the following quote from Microsoft "Also, record-level locking is not enabled for Memo data types." Another reason for moving memo fields to their own table is so that I never run into this situation Keep in mind that hyperlink and OLE Object fields involve the same pointer mechanism that memo fields do, so the above discussion applies to these data types equally well.
The other thing that I've been doing in all my released applications for the past couple of years is running code at startup, via an Autoexec macro, that uses ADO to establish record level locking for the first person to open the BE database. Subsequent users will connect to the BE database with the same locking that the initial user establishes. Michael Kaplan points out on his blog site that when a user selects the option to use Record Level locking, that this is only a request, not a demand. So, by using the ADO code in the above article, you are essentially demanding record level locking.
This KB article clearly states this for the new MVF data type in Access 2007 causes page locking.

And yes – main table had 8 memo fields, plus lot of other fields. So I moved all memo fields to a new table, added ADO code to establish record level locking and added code to save record in new table after field update in application form. For several weeks users reports that error gone!
Happy New Year!

RecordsetClone in Access 2007 forms

I did not use Access 2007 much, most of my customers still on Access 2003, which works fine. But recently, when I tested my applications on Access 2010, I found that calculating number of records in subform using RecordsetClone.RecordCount property produces #Name error. Talking about Northwind’s Orders form expression on main form’s textbox is:

=[Orders Subform].[Form].[RecordsetClone].[RecordCount]

Allen Brown mentioned it as a bug. But looks like it is not, this is a feature! Beta Engineer told me that RecordsetClone.RecordCount is replaced with Count() function (was usable in previous Access versions), which you can use it as following.  You have to add an expression field “txtCount” in the subform footer and set the control source to =Count(1). Then on the mainform add a textbox that refers to this subform control =[Orders Subform].[Form]![txtCount]. Same way like you deal with Sum() in subforms. Count() is something like Count in SQL, so in sample above 1 is a field number, and you can also use Count(*).

Count() also works in Access 2003, and would be good idea to start using in instead of RecordsetClone.RecordCount to make your applications compatible to newer Access versions.

How to list queries by type

You can get a list of all queries with simple select statement:

SELECT Name
FROM MSysObjects
WHERE Type=5

There is a Flags field in MSysObjects which shows query type, for example you can get a list of all pass-through queries:

SELECT Name
FROM MSysObjects
WHERE MSysObjects.Type=5 AND Flags in (112, 144)

And here a list of flags field values you can use (at least what i found):

• 0 – Select query
• 3 – hidden select query, used in form’s recordsource or control’s rowsource
• 16 – Crosstab query
• 32 – Delete query
• 48 – Update query
• 64 – Append query
• 80 - Make-Table query
• 96 – DDL query
• 112 - Pass-through Select query
• 128 – Union query
• 144 - Pass-through Action query

Jet tables, indexes and expressions

Michel Walsh, Access MVP, have posted an excellent example, which helps to understand how Jet deals with indexes and evaluates expressions:

Have a table, Ds, one field, D, with values for 0 to 9, and a primary key. Have, in a standard module, have:

Public Function ShowMe(Arg As Variant) As Boolean
    MsgBox "me"
    ShowMe = True
End Function

Now, make the query

SELECT d
FROM Ds
WHERE d > 100 AND ShowMe(d)

And you won't get any prompt form ShowMe.

Change the 100 to 8, and you get one prompt.

Change the 100 (or now 8) to 6, and you get three prompts (and, in A2003, a bug in the display of the result, but that is another story).

So, that is conclusive that there is a matter of short cut evaluation in the criteria and it is technically wrong to say that a function call will necessary produce a table scan. If it was true, the ShowMe would run 10 times in all the previous examples.

I have been surprised to see that

SELECT Ds.d
FROM Ds
WHERE showMe(d) AND d > 6

produces the same behavior (I was on the impression that Jet was not very optimized and did not try to re-order the WHERE parts, I was wrong).

So, indexed fields seems to be used first and if other parts are to be evaluated, then the values from these records are brought through the network, unless there are not already in the local cache.

Using Like in JOIN

While reading “The Cartesian product: a feature, not a bug” by Peter Vogel, I remember that I used one more syntax to join 2 tables. From the article – we can join 2 tables using function in expression:

SELECT Table1.Field1, *
FROM Table1, Table2Num
WHERE Table1.Field1=CStr([Field2])

But you can also use same expression in FROM clause:

SELECT Table1.Field1, *
FROM Table1 INNER JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

So you now can use Left or Right join:

SELECT Table1.Field1, *
FROM Table1 LEFT JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

And you can use whatever expression there, most useful for me was LIKE, for example you define mask for objects in table1, like AB??01??, and your query returns all objects with code for that pattern:

SELECT Table1.Field1, Table2.*
FROM Table1 LEFT JOIN Table2 ON Table2.[Field2] LIKE Table1.Field1

Of course, do not switch query to design view, then your SQL will gone…