Are unique indexes in Access text fields always case insensitive?

Question

I created an MS Access table using the following code:

tbl := Database.CreateTableDef('English', 0, '', ''); try fld := tbl.CreateField('ID', dbLong, 0); fld.Attributes := dbAutoIncrField + dbFixedField; tbl.Fields.Append(fld); fld := tbl.CreateField('Content', dbText, 255); fld.Required := true; fld.AllowZeroLength := false; tbl.Fields.Append(fld); Database.TableDefs.Append(tbl); idx := tbl.CreateIndex('PrimaryKey'); idx.Fields.Append(idx.CreateField('ID', EmptyParam, EmptyParam)); idx.Primary := True; idx.Unique := true; tbl.Indexes.Append(idx); idx := tbl.CreateIndex('IX_Content'); idx.Fields.Append(idx.CreateField('Content', EmptyParam, EmptyParam)); idx.Primary := false; idx.Unique := true; tbl.Indexes.Append(idx); finally tbl := nil; end; 

This works fine until I try to insert the two rows “Field Type” and “Field Type” into this table. I get a message that a unique index limits me to this. As you can see, they differ only in the case of the second word. Since I obviously did not make the register case insensitive (I would not even know how to do this), I do not quite understand why this is happening. Are indexes in text fields always case sensitive in MS Access? If not, what am I doing wrong?

Answer 1

Access Jet databases are mostly case insensitive. It's your problem. As far as I know, there is no way to make the access index register accessible.

Answer 2

Use binary field

The case-insensitivity issue in Microsoft Access has long been resolved in KB244693, published by Microsoft, and can still be found in the web archive .

Essentially, the solution is to add a binary field to the MS Access table, which finally is case sensitive (uses Unicode to store binary content) and can still be used as a text field with = , LIKE operators, etc.

A field of type Binary cannot be added through the user interface, but you add it to an existing table using an SQL statement, for example:

 ALTER TABLE Table1 ADD COLUMN BinaryField1 BINARY(50) 

Then you can normally manage it through the access interface.