HOWTO: Perform a Case-Sensitive JOIN via Microsoft Jet

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2, 2.5
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 4.0, 5.0, 6.0
• Microsoft ODBC Driver for Access, versions 3.0, 3.5, 3.6
• Microsoft Access versions 2000, 7.0, 97

SUMMARY

The Microsoft Jet database engine is inherently case-insensitive. When joining tables, it matches lowercase "abc" to uppercase "ABC," which in most cases is desirable. This article describes four methods of achieving a case-sensitive JOIN such that "abc" does not match "ABC."

MORE INFORMATION

NOTE: In general, case-sensitive data should not need to be stored in a database. You should be able to match "Jones" with "JONES." If you need to store case-sensitive data, such as passwords, please consider carefully the ramifications of each of the methods outlined in this article.

This article describes four methods of achieving a case-sensitive JOIN using the Microsoft Jet database engine. Each of these methods has advantages and disadvantages that should be weighed before choosing an implementation. The methods are:

• StrComp



• Case-Sensitive IISAM Driver



• Hexadecimal Expansion



• Binary Field

StrComp

SELECT Table1.ID, Table1.Description
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID 

SELECT Table1.ID, Table1.Description
FROM Table1 INNER JOIN Table2 ON StrComp(Table1.ID, Table2.ID, 0) = 0 

1. You cannot use the vbBinaryCompare identifier in the SQL statement. You must use its value (0).



2. The query is always read-only because the joined fields cannot be given a unique index and are treated as a many-to-many JOIN by the query engine.



3. This query is not viewable in the Access query designer except as SQL. An alternate version, which is viewable in the Access query designer is:




SELECT Table1.ID, Table1.Description
FROM Table1, Table2
WHERE StrComp(Table1.ID, Table2.ID, 0) = 0 

4. Neither of the two case-sensitive queries given above are optimizable and performance is poor on large tables. An alternate version that is partly optimizable is:




SELECT Table1.ID, Table1.Description
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE StrComp(Table1.ID, Table2.ID, 0) = 0 

Case-Sensitive IISAM Driver

Hexadecimal Expansion

Public Function StrToHex(X As Variant) As Variant
Dim I As Long, Temp As String
  If IsNull(X) Then Exit Function
  Temp = Space$(Len(X) * 2)
  For I = 1 To Len(X)
    Mid$(Temp, I * 2 - 1, 2) = Right$("0" & Hex$(Asc(Mid$(X, I, 1))), 2)
  Next I
  StrToHex = Temp
End Function 

  rs.AddNew
  rs!ID = Me!txtID                     ' this is the human readable data
  rs!HexID = StrToHex(rs!txtID.Value)  ' this is the Primary Key field
  rs.Update 

SELECT Table1.ID, Table1.Description
FROM Table1 INNER JOIN Table2 ON Table1.HexID = Table2.HexID 

1. The query can be updated if you place a unique index on the Hex expanded field.



2. The query can be optimized for performance.



3. The Hex expanded field occupies twice as much storage in addition to the normal field.



4. You have to manage the creation of the Hex expanded field in your code by calling the StrToHex function each time the normal field is modified.



5. The Hex expanded field can get out of sync with the normal field if the table is not edited through a controlled mechanism. You would have to provide a batch mechanism to check the table and "fix" bad Hex expanded values.



6. This function is designed for character codes in the range 0 to 255. In some code pages, UNICODE characters can exist outside this range. You would have to modify the StrToHex() function to handle this case:




Temp = Space$(Len(X) * 4)
For I = 1 To Len(X)
  Mid$(Temp, I * 4 - 3, 4) = Right$("000" & Hex$(Asc(Mid$(X, I, 1))), 4)
Next I 

The Hex expanded field would then occupy four times the space of the normal field.

Binary Field

CREATE TABLE Table1 (ID BINARY(6) PRIMARY KEY, Description TEXT(30)) 

1. You can edit the binary field as if it were a normal text field in the Access user interface.



2. The Access 95 and 97 user interfaces store the text as ANSI.



• If you access the field value through code, you have to manage the ANSI to UNICODE conversion manually:




Me!ctlID = StrConv(rs!binID, vbUnicode)
rs!binID = StrConv(Me!ctlID, vbFromUnicode)
 

• In Visual Basic, the data control does not bind to the ANSI data.



• You are not able to JOIN a BINARY field to a TEXT field because the expression service automatically converts the TEXT data to UNICODE prior to the comparison.



• You are not able to run a query with a WHERE condition on the BINARY field. The following code does not return any data:




SELECT * FROM Table1 WHERE binID = 'abc'
 

3. The Access 2000 user interface stores the data in the BINARY field as UNICODE:



• You can JOIN a TEXT and a BINARY field.



• Visual Basic controls are able to bind to the BINARY field and display it, but cannot update the data successfully. If you need to be able to update the field values, you have to manage the unbound control in code. You do not have to perform the ANSI to UNICODE conversion that is required if the data must be compatible with the Access 95 or 97 user interface.



• You can execute a query with a WHERE condition using the BINARY field:




SELECT * FROM Table1 WHERE binID = 'abc' 

• When storing UNICODE data, you need to define twice as many bytes as characters. For example, instead of declaring a field as TEXT(6), you would declare it as BINARY(12) in order to be able to store the same number of characters.

REFERENCES

Microsoft Access (and Visual Basic) Help Topics:

• CREATE TABLE



• StrConv

Additional query words:

Keywords : kbAccess kbADO kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbADO250
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2,2.5,2000,3.0,3.5,3.6,4.0,5.0,6.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto