Understanding Options Compare Database

[Gates Is Antichrist]

This is tough to search for information about, since due to its "default nature" it's sitting atop so much example code out there that it causes vast numbers of false positives. Anyway it's so stupidly documented in Access (ACC2003) that I believe it's won awards at the "Microsoft:We Are God" festivities in Redmond. I expect you'll do a better job here.

In ACC03 VBA help they do a good job with Binary and Text forms of Option Compare. In fact those two help items are possibly the best documented functions I've ever seen in a Microsoft product. Someone said to him/herself, "How can I/we write this so that the user will truly have no confusion?" No doubt the employee(s) and/or contractor(s) were sent with Klink to the Siberian Front for such insolence, but the help stayed in, and it really works.

Now, the Database case, still from ACC03 help: "Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur."

Huh? Say what? My locale is the US - so they sort in the order of the English language alphabet? Huh?

Let's try microsoft.com: the knowledgebase! And OMG, they have an article entitled "ACC: Explanation of "Option Compare Database" Statement" Hallelujah! My prayers have been answered! Now I'm going to learn what Database means here! http://support.microsoft.com/kb/98227 but - wait - it's only for Access 95 and earlier. Oh well, I'm sure they link to the article I need for ACC97 and ACC03? NOT.

Well, I read it because no doubt the meaning can't have changed THAT much since ACC95. And once again they wrote examples. Fantastic. (Examples are THE key to understanding - know it, learn it, live it.) But they never explain what Database does - only examples. Weird unintuitive examples. (If someone can explain them, please do!) They do refer to the "New Database Sort Order setting you select in the Items box of the Options dialog box" but I can't locate those in ACC03. I don't even see anything remotely similar.

BTW, in http://support.microsoft.com/kb/225878/en-us, while ingeniously defending the removal of Option Explicit by default, they indicate the Option Compare Database is the default (for Access 2000, anyway). But 2003 help says "If a module doesn't include an Option Compare statement, the default text comparison method is Binary." This makes sense and seems consistent and logical. The default behavior is for Access to cram "Database" atop new code; if you then remove or disable it, that is interpreted as choosing "Binary."

I suspect that "Option Compare Database" is no big deal and simply effectively gets me to "Option Compare Text" - but it would be nice to know for sure. After all, it's sitting atop nearly every piece of code there is.

 

[Andrew Fergus]

Your understanding is correct. I think the important thing to take from their example is the InStr function returns differing values for the same text pieces when using Option Compare Text and Option Compare Binary.

You also asked about the 'new database sort order' - you can find that under menu option Tools > Options > 'General' tab > see the 'new database sort order' dropdown box. Interestingly the article you linked to mentioned this:

The description of the Option Compare Database statement in the Microsoft Access "User's Guide" is not very clear.

Andrew

P.S. There are some other handy explanations here and here.

 

[Gates Is Antichrist]

Thank you for enduring the long ranting post and for giving practical contributions thereof. I'm still bewildered at to the "weird example" result of 2 in the KB article.

The fmsinc.com link was good, but has a phrase "respects the sort order of the database" that eludes my comprehension. Can anyone elucidate?
THIS one

 

[Andrew Fergus]

Hi

In answer to this:

The fmsinc.com link was good, but has a phrase "respects the sort order of the database" that eludes my comprehension. Can anyone elucidate?

I suspect that is referring to the 'new database sort order' option I mentioned above.

In the KB article, the 2nd example is also using the sample string :

"1<space>abc<tab>ABC"

and the outcomes:

Option Compare Database: 5
Option Compare Text: 5
Option Compare Binary: 9

show the differing outputs when searching for the value "C" in that string.

Andrew

 

[Gates Is Antichrist]

Ah, you picked the easy example. The 2 *result* is the bewildering one - not *example* 2! You knew it too! Nice Clintonesque dancearound though!
Schizophenic kind of day it seems, as I howl at your various monikers

 

[Andrew Fergus]

I honestly misread your #2. D'Oh!

I see what you mean though about the number 2 in example number 1! I can't re-create the first example because no matter how I enter the string with the space and tab, I cannot get the result they have (although the "c" part works as described). I have tried using Chr(32) and Chr(9) to build the mystr string, I have tried using their example, as well as copying the text string from a text editor - but I cannot get the Instr function to think the space is a tab like they did using any of the 'Option....' choices.

You raise a good point because you would expect the 'Option Compare Database' would return either the value for 'Option Compare Text' or 'Option Compare Binary' - but it creates it's own value.... Is it a typo? Can you recreate the value 2?

Andrew

P.S. 'howl'? With laughter I presume? You probably didn't see my previous one with 'Lonny' the banjo player....

I might keep 'Spud' for a while tho....