Skip to content
Learni
View all tutorials
Automatisation

How to Automate Microsoft Access with VBA in 2026

14 minBEGINNER
VBAMicrosoft Access
Lire en français

Introduction

VBA (Visual Basic for Applications) allows you to automate Microsoft Access to save valuable time. Whether you want to create automatic reports, validate data or generate dynamic forms, VBA is the ideal tool. This tutorial guides you from the basics to your first functional scripts. You will discover how to write clean, structured and reusable code. The goal is to transform your repetitive manual tasks into reliable automated processes.

Prerequisites

  • Microsoft Access 2016 or later installed
  • Basic knowledge of Access (tables, forms, queries)
  • Windows computer
  • Developer option enabled in Access

Enable the VBA Editor

Module1.bas
Sub PremierPas()
    MsgBox "Bienvenue dans le monde du VBA Access !"
End Sub

This first module displays a simple message box. Open the VBA editor with Alt + F11 then paste this code into a standard module to test it immediately.

Understand the Structure of a Module

A module contains procedures (Sub) and functions. Each procedure starts with Sub and ends with End Sub. Use explicit names for your macros to facilitate future maintenance.

Create a Table via VBA

ModuleCreationTable.bas
Sub CreerTableClients()
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Set db = CurrentDb
    Set tbl = db.CreateTableDef("Clients")
    With tbl
        .Fields.Append .CreateField("ID", dbLong)
        .Fields.Append .CreateField("Nom", dbText, 50)
        .Fields.Append .CreateField("Email", dbText, 100)
    End With
    db.TableDefs.Append tbl
    MsgBox "Table Clients créée avec succès."
End Sub

This code creates a Clients table with three fields. It uses the DAO object to manipulate the database structure directly from VBA.

Add Records

ModuleAjoutDonnees.bas
Sub AjouterClient()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Clients", dbOpenDynaset)
    With rs
        .AddNew
        !Nom = "Dupont"
        !Email = "dupont@email.com"
        .Update
    End With
    rs.Close
    MsgBox "Client ajouté."
End Sub

This procedure inserts a new record into the Clients table. Always use .AddNew and .Update to ensure data integrity.

Automate a Form

ModuleFormulaire.bas
Sub RemplirFormulaire()
    Dim frm As Form
    Set frm = Forms!frmClients
    frm!txtNom = "Martin"
    frm!txtEmail = "martin@email.com"
    MsgBox "Formulaire mis à jour."
End Sub

This code modifies the controls of an open form. Make sure the form is open before running the macro to avoid errors.

Export to Excel

ModuleExport.bas
Sub ExporterVersExcel()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", "SELECT * FROM Clients")
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Clients", "C:\Export\Clients.xlsx", True
    MsgBox "Export terminé."
End Sub

This macro exports client data to an Excel file. Customize the path and filename according to your needs.

Best Practices

  • Always use Option Explicit to avoid variable errors
  • Name variables explicitly (e.g. strNomClient)
  • Handle errors with On Error
  • Comment your code for maintenance
  • Test each macro on data copies

Common Errors to Avoid

  • Forgetting to close Recordsets (memory leaks)
  • Not handling "object not defined" errors
  • Using very short variable names
  • Running code on closed forms

Going Further

Deepen your VBA skills with our complete training on Learni Group.