> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# tblTempVar: Creating and Populating a Table of TempVars
- URL: https://nolongerset.com/tempvars-table/
- Published: 2022-09-29T01:20:51.000Z
- Updated: 2026-05-08T12:49:06.000Z
- Description: These three simple procedures: (1) create, (2) populate, and (3) create/delete + populate a local table with the contents of the TempVars collection.
- Author: Mike Wolfe
- Tags: TempVars, Code Library, #DesignProcedures, #Import 2026-05-20 02:59

*This article is part of the series,* [*A Safer Way to Use TempVars*](https://nolongerset.com/a-safer-way-to-use-tempvars/).

---

The [TempVars collection](https://learn.microsoft.com/en-us/office/vba/api/access.tempvars) in Microsoft Access is an intriguing alternative to global variables.

In [yesterday's article](https://nolongerset.com/populatelistboxwithtempvars/), I provided a custom function you could use to populate a list box with the contents of the TempVars collection:

[Populating a List Box with the TempVars CollectionIf you use TempVars in your Microsoft Access applications, this handy function will bring visibility to the contents of that collection.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://nolongerset.com/content/images/2022/09/TempVars-in-a-List-Box.png)](https://nolongerset.com/populatelistboxwithtempvars/)

A list box is a fine choice if you only use a handful of TempVars. However, if you are pushing the limits of the TempVars collection (it supports up to 255 TempVar objects), then a list box would not be a practical choice to display all those entries.

Today, I'll provide you with VBA code to **create and populate a local table with the contents of the TempVars collection**. And once you have the data in a table, you can do all *sorts* of things with it *(pun intended)*.

## Sample Table

Here's what the populated table looks like:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/09/image-68.png)

Because it's a table, I can easily sort by any of the columns. In this screenshot, I've sorted by the VarName column.

## The Code

I used my [Notepad++ multi-line VBA string code generation trick](https://nolongerset.com/vba-code-generation-trick/) to build the multi-line string in the **CreateTempVarsTable** procedure below.

```vba
' ----------------------------------------------------------------
' Procedure : CreateTempVarsTable
' Date      : 9/28/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/tempvars-table/
' Purpose   : Creates a local table named 'tblTempVar' to hold
'               the contents of the TempVars collection.
' ----------------------------------------------------------------
Sub CreateTempVarsTable()
    Dim s As String
    s = s & "CREATE TABLE tblTempVar" & vbNewLine
    s = s & "(VarName TEXT(255) NOT NULL PRIMARY KEY" & vbNewLine
    s = s & ",VarValue LONGTEXT NULL" & vbNewLine
    s = s & ",VarTypeName TEXT(255) NULL" & vbNewLine
    s = s & ")"

    CurrentDb.Execute s, dbFailOnError
End Sub

' ----------------------------------------------------------------
' Procedure : PopulateTempVarsTable
' Date      : 9/28/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/tempvars-table/
' Purpose   : Populates the 'tblTempVar' table with the contents
'               of the TempVars collection.
' ----------------------------------------------------------------
Sub PopulateTempVarsTable()
    Dim rsTV As DAO.Recordset
    Set rsTV = CurrentDb.OpenRecordset("tblTempVar", dbOpenTable)
    
    With rsTV
        Dim Var As TempVar
        For Each Var In TempVars
            .AddNew
            !VarName = Var.Name
            !VarValue = Nz(Var.Value, "{NULL}")
            !VarTypeName = TypeName(Var.Value)
            .Update
        Next Var
    End With
End Sub

' ----------------------------------------------------------------
' Procedure : CreateAndPopulateTempVarsTable
' Date      : 9/28/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/tempvars-table/
' Purpose   : Create or clear the 'tblTempVar' table then populate
'               it with the contents of the TempVars collection.
' ----------------------------------------------------------------
Sub CreateAndPopulateTempVarsTable()
    If DCount("*", "mSysObjects", _
              "[Type]=1 AND [Name]='tblTempVar'") = 0 Then
        'Create local temp vars table if it does not exist
        CreateTempVarsTable
    Else
        'Clear the table if it does
        CurrentDb.Execute "DELETE * FROM tblTempVar", dbFailOnError
    End If
    PopulateTempVarsTable
End Sub
```

## Quick Start Guide

1. Copy and paste the above code into a standard code module
2. Run `CreateAndPopulateTempVarsTable` from the immediate window

That's it! 

Those two steps will create and populate a local table named `tblTempVar` that holds the contents of the TempVars collection.

Of course, unless you've actually defined some TempVars, the table will be empty. There are a few ways to create and set the value of a TempVar, but the simplest is this: `TempVars!{VarName} = {VarValue}`.

For example:

```vba
TempVars!MyAwesomeVar = "Something chock full of awe"
TempVars!MyBoringVar = "Hello, world!"
TempVars!TheAnswer = 42
TempVars!TheQuestion = Null
```