Introduction to VBA in Access: What It Is For, and When You Actually Need It

Most Access databases reach a point where the built-in tools run out. A macro cannot make the decision you need, a query cannot loop through records one at a time, and someone ends up doing the same twenty clicks every Monday morning. That is the point VBA earns its place.

This is a practical introduction: what VBA is for in Access, where to write it, and the handful of patterns that cover most of what small businesses actually automate. If you already write VBA and the question is whether you should still be maintaining it, our guide to where VBA becomes a maintenance liability is the more useful read.

What VBA is, and when you need it

Visual Basic for Applications is the programming language built into Access (and Excel, Word and Outlook). It is not a separate product and there is nothing to install — it ships with the application.

You do not need it for most things. Before writing a line of code, check whether one of these already does the job:

• Queries for anything that filters, joins, groups or updates a set of records at once.
• Table-level validation rules and required fields for data quality.
• Embedded macros for opening a form, running a report or applying a filter from a button.

Reach for VBA when you need something those cannot do: acting on records one at a time, making decisions based on several conditions, talking to Excel or Outlook, handling errors gracefully, or anything a macro simply has no action for.

Where the code lives

Press Alt + F11 to open the Visual Basic Editor. You do not need the Developer tab for this, though File → Options → Customise Ribbon will add it if you prefer buttons.

Code sits in two places, and the difference matters:

• Class modules are attached to a specific form or report. Code here responds to what happens on that form — a button clicked, a field changed, a record about to be saved. In form Design View, select a control, open its property sheet, choose an event and click the ... button to jump straight to the right place.
• Standard modules are shared. Anything you want to call from more than one form belongs here, so you write it once. In the editor, Insert → Module.

Turn on Tools → Options → Require Variable Declaration before you write anything. It adds Option Explicit to every new module and forces you to declare variables, which turns a whole category of silent typo bugs into an error the moment you compile.

Sub, function, event

A Sub does something. A Function does something and hands a value back. An event procedure is a Sub that Access calls for you when something happens.

Private Sub cmdSave_Click()
    If IsNull(Me.CustomerName) Then
        MsgBox "Enter a customer name before saving.", vbExclamation
        Me.CustomerName.SetFocus
        Exit Sub
    End If
    DoCmd.RunCommand acCmdSaveRecord
End Sub

Me means "the form this code is attached to". Getting comfortable with Me.FieldName is most of what you need to read other people's Access code.

The events worth knowing

• On Click — a button was pressed.
• Before Update (on the form) — fires before a record is saved. This is where cross-field validation belongs, because setting Cancel = True stops the save.
• After Update (on a control) — the user changed a value. Use it to fill in dependent fields, such as pulling a price when a product is chosen.
• On Current — the form moved to a different record. Use it to show or hide controls based on that record.
• On Open / On Load — set defaults and apply filters as the form opens.

Validation that a table rule cannot do

Single-field rules belong on the table. VBA is for rules that involve more than one field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EndDate < Me.StartDate Then
        MsgBox "The end date cannot be before the start date.", vbExclamation
        Cancel = True
        Me.EndDate.SetFocus
    End If
End Sub

Working through records one at a time

This is the pattern people most often need VBA for, and the one most often written badly. A recordset lets you step through rows and act on each:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Orders WHERE Status = 'Pending'")

Do Until rs.EOF
    rs.Edit
    rs!Status = "Processed"
    rs.Update
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Two warnings. First, always close recordsets and release the objects — leaked recordsets are behind a large share of "Access has become slow" complaints. Second, if the job could be done by an update query, do it with an update query. Looping a hundred thousand rows to set one field is thousands of times slower than the single SQL statement that does the same thing.

Running actions and SQL from code

DoCmd.OpenForm "frmOrderDetail", , , "OrderID = " & Me.OrderID
DoCmd.OpenReport "rptInvoice", acViewPreview

CurrentDb.Execute "UPDATE Orders SET Status = 'Closed' " & _
                  "WHERE OrderDate < #2026-01-01#", dbFailOnError

Use CurrentDb.Execute with dbFailOnError rather than DoCmd.RunSQL. RunSQL shows confirmation prompts and, worse, can fail silently; dbFailOnError raises a real error you can handle.

Note the # signs around the date. Access date literals need them, and dates are the most common source of mysterious VBA failures — particularly on UK-configured machines, where a literal typed as #01/02/2026# is read as the American 2 January, not 1 February. Write dates as #2026-02-01# and the ambiguity disappears.

Error handling, in the three lines it actually takes

Every procedure that touches data should have this. Without it, an unexpected error drops the user into a code window, which is alarming and occasionally destructive.

Private Sub cmdProcess_Click()
    On Error GoTo ErrHandler

    ' work goes here

    Exit Sub
ErrHandler:
    MsgBox "Could not complete: " & Err.Description, vbCritical
End Sub

The Exit Sub before the label is not optional. Leave it out and the error handler runs every time the procedure succeeds.

Debugging

Three tools cover nearly everything:

• Breakpoints — click the grey margin beside a line, run the code, and it pauses there. F8 then steps a line at a time.
• The Immediate window (Ctrl + G) — type ?Me.CustomerID while paused to see a value, or put Debug.Print in your code to log what is happening.
• Debug → Compile — run this before you hand anything over. It catches typos and missing declarations across the whole project in one pass, rather than one crash at a time in front of a user.

What goes wrong most often

• Code where a query belongs. If you are looping to change many records, you almost certainly want an update query.
• No Option Explicit. A mistyped variable name becomes a new empty variable and the bug hides for months.
• Recordsets left open. Close them, set them to Nothing.
• Unbound forms. People rebuild in code what a bound form does for free, then have to write the saving logic themselves.
• Copies of the same routine on five forms. Put it in a standard module once.
• Ambiguous date literals. Use the #yyyy-mm-dd# form.
• No error handling anywhere. The first unexpected failure lands the user in the code editor.

Where VBA stops helping

VBA is genuinely capable, and a well-written Access application can run a small business for years. But it is tied to the format it lives in, and no amount of code moves those walls:

• The database file is capped at 2GB.
• Roughly ten concurrent users is the practical ceiling, well below the theoretical one.
• There is no browser or mobile access. Access Web Apps were discontinued in 2018 and were not replaced.
• VBA only runs where Access is installed — Windows desktops, and not the Mac version.

If the reason you are writing VBA is that people cannot reach the database from home or from a phone, or that it slows to a crawl when several of them open it at once, more code will not fix it. That is a platform limit, and the usual answer is rebuilding as a web application.

The encouraging part is that the VBA is not wasted. It is a written record of your business rules — what has to be true before a record saves, what happens after an order is approved — and those rules carry across to a new system directly. Working them out is the hard part, and it is already done.

Our Microsoft Access services page covers improving what you have as well as moving beyond it, and a free system review will tell you which one your situation calls for.