Automating Repetitive Tasks with Access VBA

Every Access database has them: the three clicks to open the daily report, the manual import of last night's CSV, the copy-paste routine that takes 20 minutes every Monday morning. VBA (Visual Basic for Applications) exists to eliminate exactly these tasks.

This guide covers the practical patterns you need to start automating your Access database — no prior programming experience required.

What VBA Can Do in Access

VBA is a full programming language embedded in every copy of Microsoft Office. In Access, it can:

• Open, filter, and print reports automatically
• Import and export data on a schedule or button click
• Validate form input beyond what built-in rules allow
• Send emails via Outlook
• Run queries and process their results
• Interact with other Office applications (Excel, Word, Outlook)

The key insight is that almost anything you can do manually in Access, you can do with VBA — and you can trigger it from a button, a form event, or a scheduled macro.

Your First VBA Procedure

Open the VBA editor with Alt+F11. You will see the Project Explorer on the left and a code window on the right. To add code to a form, double-click the form in the Project Explorer.

Here is a simple procedure that opens a report filtered to today's orders:

Private Sub btnTodaysOrders_Click()
    Dim strFilter As String
    strFilter = "OrderDate = #" & Format(Date, "mm/dd/yyyy") & "#"
    DoCmd.OpenReport "rptOrders", acViewPreview, , strFilter
End Sub

Attach this to a button on your main form and you have replaced three manual steps with one click.

Automating Data Import

If you receive a CSV file every morning and manually import it, this pattern will save you significant time:

Public Sub ImportDailyData()
    Dim strPath As String
    Dim strFile As String
    
    strPath = "C:\Data\Daily\"
    strFile = strPath & Format(Date, "yyyymmdd") & "_orders.csv"
    
    ' Check if today's file exists
    If Dir(strFile) = "" Then
        MsgBox "Today's import file not found: " & strFile, vbExclamation
        Exit Sub
    End If
    
    ' Import the file (append to existing table)
    DoCmd.TransferText acImportDelim, "ImportSpec_Orders", _
        "tblOrdersStaging", strFile, True
    
    ' Run the processing query
    DoCmd.RunSQL "DELETE FROM tblOrdersStaging WHERE Processed = True"
    
    MsgBox "Import complete.", vbInformation
End Sub

The TransferText method handles CSV imports. The "ImportSpec_Orders" argument refers to a saved import specification — create one by running a manual import and saving the spec at the end of the wizard.

Form Validation Beyond Built-In Rules

Access's built-in validation rules handle simple cases, but complex business logic needs VBA. Here is a pattern for validating a form before saving:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Require ShipDate to be after OrderDate
    If Not IsNull(Me.ShipDate) Then
        If Me.ShipDate < Me.OrderDate Then
            MsgBox "Ship date cannot be before order date.", vbExclamation
            Me.ShipDate.SetFocus
            Cancel = True
            Exit Sub
        End If
    End If
    
    ' Require at least one line item
    If Me.subOrderLines.Form.RecordCount = 0 Then
        MsgBox "Please add at least one order line before saving.", vbExclamation
        Cancel = True
    End If
End Sub

The Form_BeforeUpdate event fires every time Access tries to save a record — whether the user clicks Save, closes the form, or navigates to another record. Setting Cancel = True stops the save and keeps the user on the current record.

Sending Email Notifications via Outlook

If your team needs to know when certain events happen in the database, you can send emails directly from VBA:

Public Sub SendOrderConfirmation(OrderID As Long)
    Dim olApp As Object
    Dim olMail As Object
    Dim rs As DAO.Recordset
    
    ' Get order details
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT * FROM qryOrderDetails WHERE OrderID = " & OrderID)
    
    If rs.EOF Then
        MsgBox "Order not found.", vbExclamation
        Exit Sub
    End If
    
    ' Create the email
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
    
    With olMail
        .To = rs!CustomerEmail
        .Subject = "Order Confirmation #" & OrderID
        .Body = "Dear " & rs!CustomerName & "," & vbCrLf & vbCrLf & _
                "Your order has been received. Total: $" & _
                Format(rs!Total, "#,##0.00") & vbCrLf & vbCrLf & _
                "Thank you for your business."
        .Send
    End With
    
    rs.Close
    Set rs = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    
    MsgBox "Confirmation sent to " & rs!CustomerEmail, vbInformation
End Sub

This uses late binding (CreateObject) so it works regardless of which version of Outlook is installed.

Running Queries from VBA

Sometimes you need to run an action query (UPDATE, DELETE, INSERT) from code rather than from the query designer:

Public Sub ArchiveOldOrders()
    Dim db As DAO.Database
    Dim strSQL As String
    Dim lngCount As Long
    
    Set db = CurrentDb
    
    ' Count records to be archived
    lngCount = DCount("*", "tblOrders", "OrderDate < #1/1/2025# AND Archived = False")
    
    If lngCount = 0 Then
        MsgBox "No orders to archive.", vbInformation
        Exit Sub
    End If
    
    If MsgBox("Archive " & lngCount & " orders?", vbYesNo + vbQuestion) = vbNo Then
        Exit Sub
    End If
    
    ' Move to archive table
    strSQL = "INSERT INTO tblOrdersArchive SELECT * FROM tblOrders " & _
             "WHERE OrderDate < #1/1/2025# AND Archived = False"
    db.Execute strSQL, dbFailOnError
    
    ' Mark as archived
    strSQL = "UPDATE tblOrders SET Archived = True " & _
             "WHERE OrderDate < #1/1/2025# AND Archived = False"
    db.Execute strSQL, dbFailOnError
    
    MsgBox "Archived " & lngCount & " orders.", vbInformation
    
    Set db = Nothing
End Sub

The dbFailOnError flag is important — it causes VBA to raise an error if the SQL fails, rather than silently doing nothing.

Error Handling: The One Pattern You Must Use

Every VBA procedure that touches data should have error handling. Without it, a runtime error will leave your database in an inconsistent state and confuse your users:

Public Sub ProcessImport()
    On Error GoTo ErrorHandler
    
    ' ... your code here ...
    
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description & vbCrLf & _
           "Error number: " & Err.Number, vbCritical
    ' Log to error table if needed
    ' CurrentDb.Execute "INSERT INTO tblErrorLog ..."
End Sub

The On Error GoTo ErrorHandler line at the top redirects execution to your error handler if anything goes wrong. The Exit Sub before the label ensures the handler only runs on errors, not at the end of normal execution.

Building a Startup Routine

One of the most useful VBA patterns is a startup procedure that runs when the database opens. You can set this up in File → Options → Current Database → Display Form, or by calling it from the AutoExec macro:

Public Sub OnStartup()
    ' Check for pending imports
    If Dir("C:\Data\Daily\" & Format(Date, "yyyymmdd") & "_orders.csv") <> "" Then
        If MsgBox("Today's import file is ready. Import now?", vbYesNo) = vbYes Then
            ImportDailyData
        End If
    End If
    
    ' Open the main menu form
    DoCmd.OpenForm "frmMainMenu"
    
    ' Log the login
    CurrentDb.Execute "INSERT INTO tblLoginLog (UserName, LoginTime) " & _
        "VALUES ('" & Environ("USERNAME") & "', Now())"
End Sub

This kind of startup routine makes your database feel like a real application rather than a collection of tables and queries.

Next Steps

These patterns cover the most common automation scenarios in Access. Once you are comfortable with them, the natural next step is to explore more advanced topics: working with DAO Recordsets for complex data processing, using the FileSystemObject for file operations, or connecting to external data sources via ADO.

If your automation needs are growing beyond what Access VBA can comfortably handle, it may also be worth exploring Power Automate — Microsoft's cloud-based automation platform that integrates with Access data through Excel or SharePoint connectors.