Case sensitive joins in Access and Visual Basic (VBA)

(This post is based on work done in Access 2010.) By default, Access is not case sensitive. This can create problems when filtering or joining on fields that are unique when case is taken into account but not unique when case is disregarded. One example I've run into is with Salesforce account id's. Salesforce account id's are case sensitive. Salesforce sees XyZ123 and xYz123 as two different values whereas Access sees them as the same value. If you import Salesforce data into Access and create queries that join, filter, or aggregate on the Salesforce account id, you will get unwanted duplicates.

One way to overcome this problem is by converting each character in the Salesforce account id to the ASCII code for that character. (See Wikipedia for an in-depth explanation of ASCII character codes.) You can create a new field in your table and update the new field with the ASCII representation of the Salesforce account id. The code below loops through each character in the Salesforce ID, converts it to the ASCII hex code, and stores it in the newID variable.  

For i = 1 To Len(SFID)
   newID = newID & Hex(Asc(Mid(SFID, i, 1)))
Next i 

You would place the above code within a result set loop similar to the following code:

strSQL = "SELECT * FROM Some_Table" 
Set rst = db.OpenRecordset(strSQL) 

Do While Not rst.EOF 
   newID = ""
   SFID = Trim(rst!theSFID) 

   For i = 1 To Len(SFID) 
      newID = newID & Hex(Asc(Mid(SFID, i, 1))) 
   Next i 

   strSQL = "UPDATE Some_Table SET SFUniqueID = '" & _
   newID & "' WHERE autoID = " & rst!autoID

   DoCmd.RunSQL strSQL 
   rst.MoveNext 
Loop 

With your newly created unique id, you can do SQL joins, aggregate functions, and filtering without any inadvertent duplicate values.

If you just need to do a case-sensitive join in Access (you don't need to aggregate or filter on a key value) then you can use the strComp function in the join as follows:

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