MS Access VBA Dictionary: Setup, Examples, and Tips

VBA & macros · Updated November 17, 2026

Yes. Microsoft Access VBA can use the Scripting.Dictionary object to store key-value pairs, look up a value by its key, and check whether a key already exists. It is provided by the Windows Scripting Runtime rather than being an Access-specific collection, and you can create it in VBA with either early binding or late binding.

What a dictionary does in Access VBA

A dictionary associates each key with one item. For example, a customer number can be the key and that customer’s name can be the item. Unlike a simple array, a dictionary is designed for lookups by key; unlike a table, it lives in memory while your code runs unless you explicitly save its contents elsewhere.

Each key must be unique. A dictionary can contain many different kinds of values, including text, numbers, and, in suitable VBA code, object references. It is useful when you need to gather values, avoid processing the same key twice, or test membership repeatedly without querying a table for every lookup.

For example, while processing order rows, a dictionary could track which product IDs have already appeared. It can also hold a running count or total for each category. These are temporary in-memory tasks: closing the procedure or discarding the dictionary does not write its contents to an Access table.

Two ways to create the object

Late binding creates the dictionary at runtime. It does not require you to add a VBA reference, which can make a database easier to move between machines with different reference configurations. The variable is declared as a generic Object, so the VBA editor offers less dictionary-specific autocomplete and compile-time checking.

Public Sub CreateLateBoundDictionary()
    Dim dict As Object

    ' Create the Windows Scripting Runtime dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "CustomerID", 1042

    Debug.Print dict("CustomerID")
    Set dict = Nothing
End Sub

Early binding uses a specific dictionary type and gives you editor assistance and compile-time checking for its members. In the Visual Basic Editor, open Tools > References, select Microsoft Scripting Runtime, and then declare the variable as Scripting.Dictionary. The reference must be available on computers where the database runs; a missing reference can cause a “MISSING” entry and compile errors.

Public Sub CreateEarlyBoundDictionary()
    Dim dict As Scripting.Dictionary

    ' Requires the Microsoft Scripting Runtime reference.
    Set dict = New Scripting.Dictionary
    dict.Add "CustomerID", 1042

    Debug.Print dict("CustomerID")
    Set dict = Nothing
End Sub

For a database distributed to other users, late binding is often chosen to avoid a broken reference caused by a different reference setup. Early binding is convenient during development. Both approaches use the same dictionary behavior; choose based on deployment needs, and verify the object can be created on each target Windows computer.

Adding, checking, and retrieving values

The most-used members are Add, Exists, the default Item property, and Count. Add inserts a new key and its value. Exists returns True or False. You can retrieve an item with parentheses, as in dict("CustomerID"), and Count reports how many keys are currently stored.

A common pattern is to check before adding. Calling Add with a key already in the dictionary raises an error, so the check makes the intended behavior explicit. This example counts repeated category names:

Public Sub CountCategories()
    Dim dict As Object
    Dim categories As Variant
    Dim category As Variant
    Dim key As String

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    categories = Array("Tools", "Books", "Tools", "Games")

    For Each category In categories
        key = CStr(category)
        If dict.Exists(key) Then
            dict(key) = CLng(dict(key)) + 1
        Else
            dict.Add key, 1
        End If
    Next category

    Debug.Print dict("Tools")  ' Prints 2.
    Debug.Print dict.Count     ' Prints 3.
    Set dict = Nothing
End Sub

Here the values are counts, not records from a table. In a real Access procedure, the same logic can be applied as code reads rows from a DAO recordset: extract the category, check whether it is present, and update or add its count. If the values must remain available after the procedure ends, write the results to a table rather than relying on the dictionary.

Key comparison and the CompareMode rule

By default, string keys are compared using binary comparison, so capitalization can matter: "North" and "north" may be treated as different keys. Set CompareMode to vbTextCompare when string keys should be matched without regard to case. Use the default behavior when case-sensitive distinction is important.

Set CompareMode immediately after creating the dictionary and before adding any keys. Changing it after keys have been added causes an error. The counting example follows this order. Choose the comparison behavior deliberately: case-insensitive matching can prevent duplicate-looking names, but it can also combine values that your application considers distinct.

Comparison behavior applies to keys, not to the values stored under them. It does not trim spaces or standardize punctuation. For instance, a key with a trailing space is not automatically the same as the otherwise identical key without one. If input comes from forms or imported data, normalize it first when appropriate, perhaps by applying Trim before using it as a key.

Looping through keys and values

Use the Keys and Items properties to get arrays of the dictionary’s keys and values. The usual loop uses a Variant variable for each key, then retrieves the corresponding item. This is also a practical way to inspect what a dictionary contains while debugging.

Public Sub PrintDictionaryContents()
    Dim dict As Object
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A17", "Open"
    dict.Add "B24", "Closed"

    For Each key In dict.Keys
        Debug.Print CStr(key), dict(key)
    Next key

    Set dict = Nothing
End Sub

Do not treat a dictionary as a sorted list. If your output must be alphabetical or follow a business order, sort the keys separately or use a query with an explicit ORDER BY when working with table data. A dictionary is primarily a lookup structure, not a replacement for a sorted Access recordset.

When a dictionary is the right tool

A dictionary is a good fit when VBA needs fast, repeated membership checks or a temporary association between unique keys and values. Typical cases include counting occurrences, mapping an ID to a display value during a procedure, removing duplicate input from a working set, and accumulating totals by group before writing a result.

  • Use a dictionary when your code needs to ask whether a particular key has been seen and then retrieve or update its associated value.
  • Use an Access table when data must persist, be shared between users, participate in relationships, or be queried later.
  • Use a DAO recordset when the task is fundamentally to read or edit records in an Access table.
  • Use a VBA Collection when you need a simple collection and do not need the dictionary’s built-in Exists check or key-based replacement behavior.

A dictionary does not enforce database rules such as field types, relationships, or unique indexes. It is also local to the running VBA code and is not a substitute for a multi-user data store. For a large or persistent dataset, an Access query or table is usually the more appropriate foundation; a dictionary can still help with a temporary calculation around that data.

Common errors and practical fixes

One frequent error is adding a duplicate key. Use Exists first if duplicates are possible, and decide whether a repeat should be ignored, counted, or used to update the existing value. Assigning a value through dict(key) can create a missing entry, so use Add when you want duplicate-key insertion to be detected rather than silently following an update path.

Another common problem is a compile error mentioning a missing type or reference. If the declaration uses Scripting.Dictionary, check Tools > References in the Visual Basic Editor and resolve any missing reference. Alternatively, declare the variable as Object and use CreateObject("Scripting.Dictionary") to avoid the explicit reference. If object creation itself fails on a target PC, check that the required Windows scripting component is available and registered there.

Also watch for assumptions about keys. A Null value from an Access field needs handling before it is converted to a string; Nz is commonly used in Access to provide a replacement. Values that look identical to a person may differ because of spaces, capitalization settings, or data types. Normalize imported or form-entered keys consistently, and set CompareMode before adding any entries.

Finally, do not expect the dictionary to change a table automatically. Updating dict(key) changes the in-memory value only. Your code must explicitly run an update or append operation, or use an appropriate query, to persist the result in Access.

Frequently Asked Questions

Is Scripting.Dictionary built into Access?
It is available to Access VBA through the Windows Scripting Runtime, but it is not an Access table or an Access-only collection. You can use a reference for early binding or create it at runtime with late binding.

How do I check whether a dictionary contains a key?
Call dict.Exists(key). It returns True if the key is present and False otherwise. This is the standard check before adding a key that may already exist.

How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any keys. This changes string-key comparison, not the stored values.

Does a dictionary save its contents in the Access database?
No. Its contents are held in memory. To keep them, explicitly write them to an Access table or another persistent destination.

Should I use early binding or late binding?
Early binding provides dictionary-specific type checking and editor assistance, but requires the Microsoft Scripting Runtime reference to be available. Late binding avoids that explicit reference and can be more portable across reference configurations, though it offers less compile-time checking.

Can I use a dictionary instead of an Access table?
Only for temporary in-memory work. A dictionary does not provide persistent storage, relationships, shared multi-user data, or database constraints. Use a table for durable application data, and use a dictionary for a focused VBA lookup or calculation.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.