VBA Access

Access is a Microsoft application used to work with databases. Data can be stored in Access or Access can be used as a front end for data stored in SQL Server or Azure. VBA is embedded within the Access application and can be used to interact programmatically with Access. When working with VBA in Access, references are automatically included to the Microsoft Access Object Library (Access) and the Microsoft Office Access database engine Object Library (DAO) which give VBA access to the Access Object Model and the Data Access Object Model.

Note: Examples in this section will use the Northwind example database.

Option Compare Database

The Option Compare Database statement can be used exclusively in Access. Option Compare Database causes strings to be compared using the text sort order given the locale ID of the database where the string comparison occurs. Option Compare Database is automatically included at the top of new code modules in Access by default.

Option Compare Database
Option Explicit

Public Sub Example()

    Debug.Print "A" = "a"

End Sub

Application

The Application object is the top-level object in the Access Object Model and represents the Access Application itself. The Application object provides access to the rest of the Access Object Model and the Data Access Object Model. The Application object contains properties and methods for working with the Access application and databases.

D Functions

D functions are used to query a table or query result for information. All D functions have the same arguments: "Expr" representing the field to operate on, "Domain" representing the table or query containing the field, and "Criteria" representing an optional conditional statement.

Public Sub Example()

    Dim Result As Variant

    Result = Access.Application.DLookup("[Last Name]", "Customers", "[First Name]='John'")

    Debug.Print Result

End Sub

Function	Description
DAvg	Calculates the average of a set of values within a set of records.
DCount	Determines the count of a set of records.
DFirst	Returns the first value in a table or query.
DLast	Returns the last in a table or query.
DLookup	Look up a value from a set of records from a specified field given certain criteria.
DMax	Returns the maximum value in a set of records.
DMin	Returns the minimum value in a set of records.
DStdev	Returns the sample standard deviation from a set of records.
DStdevP	Returns the population standard deviation from a set of records.
DSum	Returns the sum of a set of values within a set of records.
DVar	Returns the sample variance from a set of records.
DVarP	Returns the population variance from a set of records.

Nz Function

The Application.Nz function is used to return a specified value when a Variant is set to Null.

Public Sub Example()

    Dim Value As Variant
    Dim Result As Variant

    Value = Null

    Result = Access.Application.Nz(Value, "Value is Null")

    Debug.Print Result

End Sub

DoCmd

The DoCmd object can be used to perform many important actions in Access.

Public Sub Example()

    Access.Application.DoCmd.OpenForm "Employee Details"

    Access.Application.DoCmd.Close acForm, "Employee Details", acSaveYes

End Sub

SysCmd

The SysCmd method can be used to perform actions and retrieve information. Use the AcSysCmdAction enum to choose an action.

Public Sub Example()

    Dim Result As Variant

    Result = Access.Application.SysCmd(acSysCmdAccessVer)

    Debug.Print Result

End Sub

CodeData | CurrentData

The CodeData and CurrentData objects have properties containing collections of AccessObject objects:

The CodeData object contains collections of AccessObject objects for the database in which the code is running. The CurrentData object contains collections of AccessObject objects for the database currently open in the Access Window.

Public Sub Example()

    Dim AO As Access.AccessObject

    For Each AO In Access.Application.CurrentData.AllTables
        Debug.Print AO.Name
    Next AO

End Sub

CodeProject | CurrentProject

The CodeProject and CurrentProject objects have properties containing collections of AccessObject objects:

The CodeProject object contains collections of AccessObject objects for the database in which the code is running. The CurrentProject object contains collections of AccessObject objects for the database currently open in the Access Window.

Public Sub Example()

    Dim AO As Access.AccessObject

    For Each AO In Access.Application.CurrentProject.AllForms
        Debug.Print AO.Name
    Next AO

End Sub

References

The Reference object represents a VBA project reference. The References collection object contains a collection of Reference objects. The Application.References property can be used to retrieve the References collection. References can be added using the AddFromFile method and the AddFromGUID method.

Public Sub Example()

    Dim R As Access.Reference

    For Each R In Access.Application.References
        Debug.Print R.Name
    Next R

End Sub

Modules

The Module object represents a code module. The Modules collection object contains a collection of Module objects. The Modules collection object can be accessed from the Application.Modules property.

Public Sub Example()

    Dim i As Long

    For i = 0 To Access.Application.Modules.Count - 1
        Debug.Print Access.Application.Modules(i).Name
    Next i

End Sub

Printers

The Printer object represents an available printer. The Printers collection object contains a collection of Printer objects. The Application.Printers property can be used to retrieve a Printers collection object.

Public Sub Example()

    Dim P As Access.Printer

    For Each P In Access.Application.Printers
        Debug.Print P.DeviceName
    Next P

End Sub

Forms

The Form object represents a Form in Access. The Forms collection object contains a collection all currently open Form objects. The Forms collection object can be accessed from the Application.Forms property.

Public Sub Example()

    Dim F As Access.Form

    For Each F In Access.Application.Forms
        Debug.Print F.Name
    Next F

End Sub

Controls

The Controls property can be used to retrieve a Controls collection containing all Control objects on a Form.

Reports

The Report object represents a report in Access. The Reports collection object contains all open reports in Access. The Application.Reports property can be used to retrieve the Reports collection object.

Public Sub Example()

    Dim R As Access.Report

    For Each R In Access.Application.Reports
        Debug.Print R.Name
    Next R

End Sub

DBEngine

The DBEngine object is the top-level object in the DAO object model and can be used to work with databases. The DBEngine object can be retrieved through the Application.DBEngine property.

Public Sub Example()

    Dim DBE As DAO.DBEngine
    Set DBE = Access.Application.DBEngine

    Dim DB As DAO.Database
    Set DB = DBE.OpenDatabase("Northwind")

End Sub

Workspace

The Workspace object represents a named session for a user. The Workspaces collection object contains a collection of Workspace objects. The Workspaces collection object can be accessed through the DBEngine.Workspaces property.

Public Sub Example()

    Dim DBE As DAO.DBEngine
    Set DBE = Access.Application.DBEngine

    Dim WS As DAO.Workspace

    For Each WS In DBE.Workspaces
        Debug.Print WS.Name
    Next WS

End Sub

Databases

The Database object represents a database. The Databases collection object contains a collection of Database objects. The Databases collection object can be accessed through the Workspace.Databases property. Database objects can be retrieved using the CodeDb, CurrentDb, and OpenDatabase methods.

CodeDb | CurrentDb

The CodeDb method returns a Database object associated with the database in which the code is running. The CurrentDb method returns the database currently open in the Access Window. If the database associated with the code is currently open in the Access Window then these methods return the same Database object.

Public Sub ExampleDAO()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    Set DB = Access.Application.CurrentDb
   'Set DB = Access.Application.CodeDb

    Set RS = DB.OpenRecordset("Customers")

    Do While Not RS.EOF
        Debug.Print RS.Fields("Last Name").Value
        RS.MoveNext
    Loop

End Sub

OpenDatabase

The DBEngine.OpenDatabase method can be used to open a database and retrieve a Database object.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.DBEngine.OpenDatabase("Northwind")

End Sub

Iterate Databases

To loop over databases, use the Databases property of the Workspace object with a For Each loop.

Public Sub Example()

    Dim DB As DAO.Database

    For Each DB In Access.DBEngine.Workspaces("#Default Workspace#").Databases
        Debug.Print DB.Name
    Next DB

End Sub

Execute

The Execute method can be used to execute SQL statements on a Database. The Execute method takes two parameters, an SQL query statement string and Options as a member of the RecordsetOptionEnum enum. To determine the outcome of the Execute method use the Database.RecordsAffected property.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    DB.Execute "UPDATE Customers " & _
               "SET [E-mail Address] = [Last Name] + '@email.com' " & _
               "WHERE [Company] = 'Company A'"

    Debug.Print "Records Affected: " & DB.RecordsAffected

End Sub

Recordsets

The Recordset object represents the records in a table or records returned from a query. The Recordsets collection object contains a collection of all open Recordset objects within a Connection or Database and can be accessed through the Recordsets property of a Connection or Database object.

Overview

The Recordset object contains records (rows) which are contain fields (columns). The Recordset object has a cursor which points to a one record at a time. This cursor can be moved forward, and depending on the type of Recordset backward as well, to point at different Records. There are five types of Recordsets that can be used that each behave differently: Table, Dynaset, Snapshot, Forward-only, and Dynamic.

Type	Description
Table	Represents a single table in a database. Can add, alter, and remove records.
Dynaset	Represents the result of a query. Can add, alter, and remove underlying records for one or more tables.
Snapshot	Represents a static copy of a records from a table or tables. Cannot affect records in the underlying table(s).
Forward‑only	Same as Snapshot but can only move forward through records. Improved performance.
Dynamic	Represents the result of a query. Can add, alter, and remove underlying records for one or more tables. Changes made by other users appear in the Recordset.

Open a Recordset

To open a Recordset, use the OpenRecordset method with a table name or SQL string. Specify the type of Recordset using the RecordsetTypeEnum enumeration.

Public Sub Example()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    Set DB = Access.Application.CurrentDb

    Set RS = DB.OpenRecordset("Customers")

End Sub

Public Sub Example()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    Set DB = Access.Application.CurrentDb

    Set RS = DB.OpenRecordset("SELECT * FROM Customers WHERE [State/Province] = 'CA'")

End Sub

Fields

The Field object represents a field in a Recordset. The Fields collection object contains a collection of Field objects. Access the Fields collection through the Recordset.Fields property. To return the value of a Field use the Field.Value property. The values stored in each field are the values for the current record that the Recordset's cursor is pointing at. To move the cursor use the MoveFirst, MoveLast, MoveNext, MovePrevious, and Move methods.

Public Sub Example()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    Set DB = Access.Application.CurrentDb

    Set RS = DB.OpenRecordset("Customers")
   'Set RS = DB.OpenRecordset("SELECT * FROM Customers WHERE [State/Province] = 'NY'")

    Do While Not RS.EOF
        Debug.Print RS.Fields("Last Name").Value
        RS.MoveNext
    Loop

End Sub

Connections

The Connection object represents a connection to a database. The Connections collection object contains a collection of Connection objects and can be accessed through the Workspace.Collections property. The Connection object is only available for ODBCDirect workspaces.

A Connection object can be retrieved for the CodeProject or CurrentProject through the CodeProject.Connection property and the CurrentProject.Connection property.

TableDefs

The TableDef object represents the stored definition for a base table or linked table. The TableDefs collection object contains all the TableDef objects in a database. Use the Database.TableDefs property to access the TableDefs collection.

Create Table

To create a new table first instantiate a new TableDef object or use the Database.CreateTableDef method. Next create and append fields using the TableDef.CreateField method and the Fields.Append method. For the CreateField method pass a Name for the field, a Type to determine the field's data type, and a Size to determine the allowable size the field can store. Attributes of fields can be set using the Field.Attributes property. Lastly, append the TableDef object to the TableDefs collection using the TableDefs.Append method and call the TableDefs.Refresh method.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim TD As DAO.TableDef
   'Set TD = New DAO.TableDef
   'TD.Name = "People"
    Set TD = DB.CreateTableDef("People")

    TD.Fields.Append TD.CreateField("ID", dbLong)
    TD.Fields("ID").Attributes = dbAutoIncrField
    TD.Fields.Append TD.CreateField("Last Name", dbText, 50)
    TD.Fields.Append TD.CreateField("First Name", dbText, 50)
    TD.Fields.Append TD.CreateField("Job Title", dbText, 50)

    DB.TableDefs.Append TD
    DB.TableDefs.Refresh

End Sub

QueryDefs

The QueryDef object represents a stored definition of a query. The QueryDefs collection object contains all the QueryDef objects in a database. Use the Database.QueryDefs property to access the QueryDefs collection object.

Create Query

To create a new query, instantiate a new QueryDef object, set the SQL and Name properties, and use the QueryDefs.Append method to add the new QueryDef object to the Database's collection of queries.

Public Sub Example()

    Dim QD As DAO.QueryDef
    Set QD = New DAO.QueryDef

    QD.SQL = "SELECT * FROM Customers"
    QD.Name = "CustomersQuery"

    Access.Application.CurrentDb.QueryDefs.Append QD

End Sub

The CreateQueryDef method can also be used to create and add a new query.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim QD As DAO.QueryDef
    Set QD = DB.CreateQueryDef("CustomersQuery", "SELECT * FROM Customers")

End Sub

Parameters

The Parameter object represents a parameter to a query. The Parameters collection object contains all the Parameter objects for a query. Use the QueryDefs.Parameters property to access a QueryDef object's parameters.

Option Compare Database
Option Explicit

Public Sub CreateSelectQuery()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim QD As DAO.QueryDef
    Set QD = DB.CreateQueryDef("CustomerNameQuery")

    QD.SQL = "PARAMETERS CustomerFirstName TEXT, CustomerLastName TEXT; " & _
             "SELECT * FROM Customers " & _
             "WHERE [First Name] = [CustomerFirstName] AND [Last Name] = [CustomerLastName];"

End Sub

Public Sub RunSelectQuery()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim QD As DAO.QueryDef
    Set QD = DB.QueryDefs("CustomerNameQuery")

    QD.Parameters("CustomerFirstName").Value = "John"
    QD.Parameters("CustomerLastName").Value = "Edwards"

    Dim RS As DAO.Recordset
    Set RS = QD.OpenRecordset

    Do While Not RS.EOF
        Debug.Print RS.Fields("First Name") & " " & RS.Fields("Last Name")
        RS.MoveNext
    Loop

End Sub

Option Compare Database
Option Explicit

Public Sub CreateActionQuery()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim QD As DAO.QueryDef
    Set QD = DB.CreateQueryDef("CustomerNameActionQuery")

    QD.SQL = "PARAMETERS CustomerFirstName TEXT, CustomerLastName TEXT; " & _
             "UPDATE Customers SET [E-mail Address] = [Last Name] + '@email.com' " & _
             "WHERE [First Name] = [CustomerFirstName] AND [Last Name] = [CustomerLastName];"

End Sub

Public Sub ExecuteActionQuery()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim QD As DAO.QueryDef
    Set QD = DB.QueryDefs("CustomerNameActionQuery")

    QD.Parameters("CustomerFirstName").Value = "John"
    QD.Parameters("CustomerLastName").Value = "Edwards"

    QD.Execute dbFailOnError

    Debug.Print "Records Affected: " & QD.RecordsAffected

End Sub

Indexes

The Index object can be used to determine if a table allows duplicate records, if certain fields are required, and allows Primary or Composite keys to be set for the table. The Indexes collection object contains a collection of Index objects. Use the TableDef.Indexes property to access the Indexes collection of a table. To add an Index object to the Indexes collection use the Indexes.Append method.

Add Primary Key

To make a field the Primary key of a table create an Index object and add a corresponding field to the Fields collection of the Index object and set the Primary property to True.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim TD As DAO.TableDef
    Set TD = DB.CreateTableDef("People")

    TD.Fields.Append TD.CreateField("ID", dbLong)
    TD.Fields("ID").Attributes = dbAutoIncrField
    TD.Fields.Append TD.CreateField("Last Name", dbText, 50)
    TD.Fields.Append TD.CreateField("First Name", dbText, 50)
    TD.Fields.Append TD.CreateField("Job Title", dbText, 50)
    DB.TableDefs.Append TD
    DB.TableDefs.Refresh

    Dim IX As DAO.Index
    Set IX = TD.CreateIndex("IDIndex")
    IX.Primary = True
    IX.Fields.Append IX.CreateField("ID")
    TD.Indexes.Append IX
    TD.Indexes.Refresh

End Sub

Add Composite Key

To make a Composite Key out of more than one field create an Index object and add corresponding fields to the Fields collection of the Index object and set the Primary property to True.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim TD As DAO.TableDef
    Set TD = DB.CreateTableDef("People")

    TD.Fields.Append TD.CreateField("ID", dbLong)
    TD.Fields("ID").Attributes = dbAutoIncrField
    TD.Fields.Append TD.CreateField("Last Name", dbText, 50)
    TD.Fields.Append TD.CreateField("First Name", dbText, 50)
    TD.Fields.Append TD.CreateField("Job Title", dbText, 50)
    DB.TableDefs.Append TD
    DB.TableDefs.Refresh

    Dim IX As DAO.Index
    Set IX = TD.CreateIndex("IDLastNameFirstNameIndex")
    IX.Primary = True
    IX.Fields.Append IX.CreateField("ID")
    IX.Fields.Append IX.CreateField("Last Name")
    IX.Fields.Append IX.CreateField("First Name")
    TD.Indexes.Append IX
    TD.Indexes.Refresh

End Sub

Relations

The Relation object represents a relationship between fields in tables and queries. The Relations collection object contains all the stored Relation objects in a database. Use the Database.Relations property to retrieve the Relations collection object for a database.

Create Relationship

To create a relationship between fields in tables create a Relation object using the Database.CreateRelation method and append the Relation object to the Relations collection of the Database object using the Relations.Append method. Set the Relation.Attributes property of the Relation object using a member of the RelationAttributeEnum enum to determine whether the relationship is one-to-one or one-to-many, how to enforce rule integrity, whether Access should perform cascading update or delete operations, and the type of join to use between two tables.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim TD As DAO.TableDef
    Set TD = DB.CreateTableDef("People")

    TD.Fields.Append TD.CreateField("ID", dbLong)
    TD.Fields("ID").Attributes = dbAutoIncrField
    TD.Fields.Append TD.CreateField("Last Name", dbText, 50)
    TD.Fields.Append TD.CreateField("First Name", dbText, 50)
    TD.Fields.Append TD.CreateField("Job Title", dbText, 50)
    DB.TableDefs.Append TD
    DB.TableDefs.Refresh

    Dim IX As DAO.Index
    Set IX = TD.CreateIndex("IDIndex")
    IX.Primary = True
    IX.Fields.Append IX.CreateField("ID")
    TD.Indexes.Append IX
    TD.Indexes.Refresh

    Dim R As DAO.Relation
    Set R = DB.CreateRelation("CustomersOnPeople")
    R.Table = "Customers"
    R.ForeignTable = "People"
    R.Fields.Append R.CreateField("ID")
    R.Fields("ID").ForeignName = "ID"
    R.Attributes = dbRelationUnique
    DB.Relations.Append R
    DB.Relations.Refresh

End Sub

Iterate Relationships

Use a For Each loop to iterate over all Relation objects in the Relations collection for a Database object.

Public Sub Example()

    Dim DB As DAO.Database
    Set DB = Access.Application.CurrentDb

    Dim R As DAO.Relation
    For Each R In DB.Relations
        Debug.Print "''''''''''''''''''''''''''''''''''''''''''''''''''"
        Debug.Print R.Name
        Debug.Print R.Table
        Debug.Print R.ForeignTable
        Debug.Print "''''''''''''''''''''''''''''''''''''''''''''''''''"
    Next R

End Sub