How to get at Jet warnings that are not errors

by Michael S. Kaplan, published on 2005/10/19 13:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/10/19/482694.aspx

(this article is reconstituted from several different tips I wrote for publications, years ago)

VB, VBA, and .NET for that matter work on an exception based model for error handling (in VB and VBA they are called errors but they are implemented as exceptions, anyway).

Usually, this is fine. But Jet's internal API can return either warnings (which come back as positive numbers) or errors (which come back as negative numbers).

Now warnings are often ignorable. A few examples of ones you would often have ignored even if you could have trapped the value:

• 1039 -- returned by Jet when you do a seek for "<=" or ">=" and it did not find an exact match
• 1058 -- no idle activity occurred (happens when you tell Jet to do its idle processing with e.g. a DBEngine.Idle() call
• 1208 -- the database you just opened was encrypted
• 1301 -- the table you just opened is empty
• 1616 -- cannot undo the update if it is committed
• 1624 -- additional records beyond the ones you updated were also updated, due to referntial integrity rules
• 1625 -- additional records beyond the ones you deleted were also deleted, due to referntial integrity rules
• 1626 -- additional records beyond the ones you updated/deleted were also updated and deleted, due to referntial integrity rules
• 5032 -- could not find the BDE (Borland Database Engine) to open the Pardox database, so the internal engine was used

But I am sure you can imagine situations where these errors would be useful.

And if you use Access a lot, you will notice times that Access is taking advantage of some of these warnings to give a better user experience -- for example that 1616 one.

Unfortunately, there is no way to get those error values if you are just a regular developer.

Wait, it turns out there is!

If you look at the ADO Error object, it contains a NativeError property. And the Jet OLE DB provider actually uses this property to return the internal Jet error/warning information from Jet API calls!

In addition, the Error object has a SQLState property that contains the string resource that contains the error returned to the user when there is an error (often many errors will map to the same string, do you can think of the NatuveError property as returning more information even in the case of errors!).

The way it is stored is that the two 16-bit numbers for the major and minor error are both jammed into the 32-bit NativeError property (in my experience when there is no minor error, the major error is put in both pieces).

EXAMPLE: so let us say that the 1624 warning was returned after an update operation on some ADO connection. The connection's Error object would have one member even though no error was raised to you. The number in the NativeError property will be 106432088. Obviously not too useful unless you look at it hexidecimal form: 0x06580658. Now you can see it is error 0x0658, which is to say error 1624!

Now, to move beyond interesting theory into a place that you may want to use. :-)

There is the Jet 4.0 feature of record level locking that people love, so I will talk about that.

Now the rule is that once the database is opened (by anybody), that is the mode of the datasbase until all user are out of it; this makes sense since locking schemes have to apply to all concurrent users to have any meaning.

So what this means is that the Access setting in the Tools|Options Advanced tab (the chekbox "Open databases using record-level locking") is a request, not a demand. If the database is already opened in the other mode, your open will not fail, and with the current design you are not even warned that your preference was not heeded since your request could not be honored.

But two additional warnings that you my find useful here exist:

• 3811 -- Attempt to open the datasbase with the requested record level locking failed; page level locking will be used
• 3812 -- Attempt to open the datasbase with the requested page level locking failed; record level locking will be used

What this means is that if in your database you run the following code after it is open:

    Set con = New ADODB.Connection
    con.Provider = "Microsoft.JET.OLEDB.4.0"
    con.Properties("Data Source") = CurrentDb.Name
    con.Properties("Jet OLEDB:Database Locking Mode") = 1 ' use 0 for page level check
    con.CursorLocation = adUseServer
    con.Open

and the database was not in the mode you asked for tht you will see one of those warnings in the NativeError property, right in con.Errors(0).NativeError! You will see one of the following values (depending on whether you set the locking mode to 0 or 1 in the code) if there is something in that collection (on 100% success it will be empty):

• 249761507 -- Warning 3811 above (in hex it is 0x0ee30ee3, and 0x0ee3 is indeed 3811)
• 249827044 -- Warning 3812 above (in hex it is 0x0ee40ee4, and 0x0ee4 is indeed 3812)

So it is as simple as that. You can obviously combine this with an Application.GetOption("Use Row Level Locking") to see if the user running the code is the cause of the problem so you can kick them out or whatever. This becomes a much cooler way to solve problems such as those documented in KB article 237333, which just tells people to change the setting in Access and does not help people to fix the problem of the fact that it is not always that user's settings which are causing the problem....

# Michael S. Kaplan on 19 Oct 2005 2:48 PM:

# Jerry Pisk on 20 Oct 2005 2:22 PM:

# Michael S. Kaplan on 20 Oct 2005 4:24 PM:

# Morocco Mole on 20 Oct 2005 7:54 PM:

# Michael S. Kaplan on 20 Oct 2005 10:31 PM:

referenced by

go to newer or older post, or back to index or month or day