tblTempVar: Creating and Populating a Table of TempVars

This article is part of the series, A Safer Way to Use TempVars.

The TempVars collection in Microsoft Access is an intriguing alternative to global variables.

In yesterday's article, I provided a custom function you could use to populate a list box with the contents of the TempVars collection:

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:

The Code

I used my Notepad++ multi-line VBA string code generation trick to build the multi-line string in the CreateTempVarsTable procedure below.

' ----------------------------------------------------------------
' 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:

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