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:

Populating a List Box with the TempVars Collection

If you use TempVars in your Microsoft Access applications, this handy function will bring visibility to the contents of that collection.

No Longer SetMike Wolfe

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