How To Perform a Case-Sensitive JOIN Through Microsoft Jet (244693)

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

The simplest method of achieving a case-sensitive JOIN is to use the StrComp function to compare fields instead of the equals operator. If you have a password-lookup, this would be the most suitable method.

Case-insensitive JOIN:

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

Case-sensitive JOIN:

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

NOTES:

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

This method involves storing the data in a case-sensitive IISAM format, such as Paradox. Joins between Paradox tables are inherently case-sensitive. The drawback is that you need Paradox in addition to Access in order to design the tables and indexes and perform other maintenance on the tables. In addition, the IISAM driver performance is less than if you are using native Jet tables. This method should only be considered if you already have the data in this format.

Hexadecimal Expansion

This method involves creating a calculated column that stores a string of hexadecimal digits created from the field you want to JOIN on. A function, such as the following, can be called just after altering the key field:

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
				

Called as follows, the function converts a string "ABC" into "414243," while "abc" is converted to "616263":

  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
				

You can make the Hex expanded field the primary key. The query would be:

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

NOTES:

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

This method involves storing the text in a Binary field. The Access user interface does not allow creation of Binary fields, so you have to use a Data Definition SQL statement, such as:

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

NOTES:

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.