How can I make "Æ"="AE" -> false ?

[tombirch]

In my danish version of microsoft Access 2000 I have an odd problem:
If I type the following in the imediate window I get a true result.
print "Æ"="AE"
true

Is is possible to disable this language setting? so that I get a FALSE result?

It is very important for my data that "Æ" is not equal to "AE" as I have an identifier (index) that contains "Æ".

I have tried changing my international setting in the control panel to English, but it still dosn't help.
my workaround has been to use:
strcomp("Æ","æ",vbBinaryCompare)=0

But this is very impractical in code and slow too.

 

[RickSpr]

Setting the language in the Control Panel doesn't work because the language is stored in the database when it's created. You need to change it in the database. The only way to do this is with code.

You have two options: You can change the language for the entire database, or for just the field(s) where you need it to be different. However, think carefully before you proceed. I don't know what all the consequences of changing the database language may be, but I'm sure you'll affect the ordering of rows in any datasheet that orders by the field. You may also change group totals in reports. And you may have dependencies on the collating sequence in your VBA code.

If you decide to change the database's language, you have to do it during a compact. The user interface doesn't provide a way to change the locale, but the DBEngine.CompactDatabase method does. However, the method doesn't allow you to compact the running database, so you'll have to create another database to contain the code. Also, the method doesn't let you use the same name for output as input, so you'll have to output to another file and rename it manually.

If you decide to change a field's language, you do it by setting the Field object's CollatingOrder property. Unfortunately, this property is read-only after it's been appended to the Fields collection of the TableDef, so what you'll have to do is (in code) add a new field to the table. Then you can run an update query to copy the old field to the new. Delete the old field and rename the new one, and I think you're in business.

    Dim db As Database, tdf As TableDef
    Dim fld As Field
    Set db = CurrentDb()
    Set tdf = db.TableDefs("TableName")
    Set fld = New DAO.Field
    With fld
        .Name = "TempField"
        .CollatingOrder = dbSortGeneral
        (set other properties)
    End With
    tdf.Fields.Append fld
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing

Another method just occurred to me, that might work, if you want to change the whole database's language. Change your Control Panel setting to English, then create a new database and import everything from the old database. I'm not sure it'll work, because I don't know if an imported table would retain its CollatingOrder property, but it's quick and certainly worth a try. Rick Sprague

 

[tombirch]

Thank you very much for your help.
Your tip op importing into a new database worked fine, and fixed the problem. It turned out that for a database with collatingorder for Danish/Norwegian "Æ" is not equal to "AE", but equal for collatingorder "General".

I tried your code too, but it didn't work, I got an error saying that the collatingorder property is readonly. :-(

Regards

Tom

 

[RickSpr]

Sorry, Tom. The Help file was ambiguous on CollatingOrder being read-only. In one place it says it is, but in another it says: "You can set the CollatingOrder property of a new, unappended Field object if you want the setting of the Field object to differ from that of the Database object that contains it."

I thought that statement would apply, but apparently not. But I'm glad you could import everything into another table to fix the problem. Rick Sprague