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:

(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:

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:

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

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):

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:

Just to be clear, there is no "secret squirrel" NDA only info in this post; all of it was found independent of such knowledge. :-)

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

How reliable is this? As in, is this documented somewhere or is the Jet provider team free to change this at will? Using something just because it's there now is asking for trouble, remember people using the high bit of their pointers because Windows was ignoring it or using the the reserved properties in critical sections to see who holds it.

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

Hi Jerry,

This is actually very reliable as it is public information in the documentation of the Jet OLE DB provider. Further, Jet errors themselves have never changed in the history of Jet.

So I would say this is info you can count on....

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

>> "secret squirel"

Pssst! That's "secret squirrel," Boss.

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

Thats what I have there!

(smiling innocently, hiding the magic marker that wrote in the extra 'r')

Please consider a donation to keep this archive running, maintained and free of advertising.
Donate €20 or more to receive an offline copy of the whole archive including all images.

referenced by

2006/01/08 Is the Jet no longer invisible?

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