by Michael S. Kaplan, published on 2007/04/17 03:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2007/04/17/2159574.aspx
You may not use Microsoft Access much, and if you do use Access you may not use DAO much.
So if you want to skip this post you should feel free to do so. :-)
Here is some code:
Sub WhatAreYouTryingToProveAnyway()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Debug.Print "Creating database with Turkish collation."
Set db = DBEngine(0).CreateDatabase("test.mdb", dbLangTurkish)
Debug.Print "Creating a table with one text field and making it the primary key."
Set tdf = db.CreateTableDef("Table1")
tdf.Fields.Append tdf.CreateField("Field1", dbText, 255)
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Fields.Append idx.CreateField("Field1")
idx.Primary = True
tdf.Indexes.Append idx
db.TableDefs.Append tdf
Debug.Print db.TableDefs("Table1").Indexes.Count & " index(es) in Table1."
Set rs = db.OpenRecordset("Table1", dbOpenTable)
Debug.Print "Adding two rows to the table, one with U+0069 and the other with U+0049."
rs.AddNew
rs!Field1 = ChrW$(&H69)
rs.Update
rs.AddNew
rs!Field1 = ChrW$(&H49)
rs.Update
db.Close
Debug.Print "Compacting database into dbLangGeneral collation."
DBEngine.CompactDatabase "test.mdb", "test1.mdb", dbLangGeneral
Kill "test.mdb"
Name "test1.mdb" As "test.mdb"
Set db = DBEngine(0).OpenDatabase("test.mdb")
Debug.Print db.TableDefs("Table1").Indexes.Count & " index(es) in Table1."
db.Close
End Sub
If you run this procedure in Access, here is what will show up in the ipane (the debug window):
Creating database with Turkish collation.
Creating a table with one text field and making it the primary key.
1 index(es) in Table1.
Adding two rows to the table, one with U+0069 and the other with U+0049.
Compacting database into dbLangGeneral collation.
0 index(es) in Table1.
Hmmm... what happened to the index?
Well, Jet is always case insensitive, but due to a fortuitous and unintentionally well-timed bit of data/code theft, it fully supports proper Turkic casing (discussed previously).
So, with a database created with Turkic collation, you can put U+0069 and U+0049 in the same column without getting any kind of error.
Unfortunately, when you compact into a new collation that is not quite so understanding, Jet has two choices:
1) It could fail the compact, or
2) It could drop the index.
Clearly they chose to go with the the prize behind curtain #2....
For what it's worth, this is not what happens with Jet [Red]'s cousin, ESE (Jet Blue). For that engine, the indexes are themselves per language, and should you want to change the language you would have to drop the index and then add a new one.
As a behavior, I understand why the Jet Red folks might have made this choice, especially given how hard of a time people would have trying to fix the problem. But since this could break applications in regard to both behavioral expectations and actual runtime errors based on code assuming indexes were there when they suddenly weren't.
(This behavior is not specific to Turkic, but that was the easiest repro to show off here!)
Probably worthy of a KB article, maybe it could even re-use the title of this post, what do you think? :-)
Now I will leave the SQL Server behavior in this case (which does not match the Jet behavior) as an exercise for the reader. If it helps muddy the waters and plague SQL developers with self-doubt, they inherited that same fortuitous data change supporting Turkic collations....
This post brought to you by İ (U+0130, a.k.a. LATIN CAPITAL LETTER I WITH DOT ABOVE)