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

How to create a reusable VBA code base

(This post is based on work done in Access 2010.) Over time, you will no doubt find or create useful VBA functions that can be used in multiple Access or Excel applications. These functions probably won't ever change or will need to be updated infrequently. Rather than copying and pasting your functions into multiple Access databases, you can put the code you want to reuse in a module in a database on your network. Then you can set the database as a reference in databases that need to use your functions. Here's how you do it:

• Open the Visual Basic editor in your Access database.
• Go to Tools-->References.
• Click Browse.
• Browse to the location of the Access database that contains the code you want to reuse.
• Change the Files of Type selector if necessary.
• Click Open.
• Make sure the checkbox next to your code base database is checked in the list of Available References.
• Click OK.