How to add a Browse button to an Access form

First create a form similar to the one depicted here:

Name the browse button "cmdBrowse," name the textbox "txtFile," and name the Import File button "cmdImport." Write the click event of the Browse button as follows:

Private Sub cmdBrowse_Click()
    Dim success As Boolean
    success = browseFiles(Me.txtFile)
End Sub

Create the following function:

Public Function browseFiles(tbx As TextBox) As Boolean
   Dim fileDlg As Office.fileDialog
   Dim theFile As Variant

   ' Clear the textbox contents.
   tbx.Value = ""

   ' Instantiate the File Dialog.
   Set fileDlg = Application.fileDialog(msoFileDialogFilePicker)

   With fileDlg
       
      ' Set the title of the dialog box. '
      .Title = "Please select one or more files"

      ' Clear the filters and add the ones you want.'
      .Filters.Clear
      '.Filters.Add "Access Databases", "*.ACCDB"
      '.Filters.Add "Access Projects", "*.ADP"
      '.Filters.Add "Text Files", "*.TXT"
      '.Filters.Add "CSV Files", "*.CSV"
      .Filters.Add "Excel xlsx files", "*.XLSX"
      .Filters.Add "Excel files", "*.XLS"
      '.Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method
      ' returns True, the user picked at least
      ' one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then

         'Get the selected file
         For Each theFile In .SelectedItems
            tbx.Value = theFile
         Next

      End If
   End With
  
   browseFiles = True
  
End Function

Notice how you can add more file types to the file dialog. I've commented out all of the non-Excel file types since the form will be used to import Excel files only. 

Regular Expressions in Access VBA

To use regular expressions in Access, make sure you add Microsoft VBScript Regular Expressions as a reference in your application. The following example was created in Access 2010.

Regular expressions are always a bit mysterious to the uninitiated. It helps to have a concrete example to get started. Once you break down the meaning of each piece of the expression, the whole thing starts making sense.

In this example, I'm validating social security numbers (SSNs). I wanted a validator that would work with or without the dashes. Here's the pattern used in the validator function:

re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"

The "^" indicates the start of the line or string. The "\d{3}" means the SSN has to start with 3 digits. The "-?" means the 3 digits can be followed by 0 or 1 occurrence of a dash. "\d{2}" means there must be 2 digits following the dash (if there is one). Then we check for 0 or 1 occurrence of a dash again ("-?") followed by 4 more digits ("\d{4}"). The "$" indicates the end of the line or string.

If you wanted to validate dash-free SSNs, you would write an expression like this: "^d{9}$". This expression simply checks for 9 consecutive digits.

For more information on how to make your own pattern-matching expressions, see the Wikipedia article on regular expressions.

Here's the complete validator function:

Public Function validateSSN(ssn As String) As Boolean

    Dim re As RegExp
    Dim matches As MatchCollection
    Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"
    Set matches = re.Execute(ssn)
    If matches.Count <> 1 Then
        validateSSN = False
        Exit Function
    End If
    
    validateSSN = True
        
End Function

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.