So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables!

So What Are Self-Healing Object Variables?

In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session.  If reset, they will reinitialize again automatically.  They are like Global Variables on steroids. It is just one more step you can take as a developer to improve the overall performance and give your application a little boost, and this for any VBA application (not just Access)!

My first recollection of being exposed to the concept was back in, I believe 2003-2005ish, by MVP Dirk Goldgar who was answering one of my Access question in a discussion forum. He didn’t call it SHOV, but that is effectively what it was. Implementing it drastically helped with performance with a database I was struggling with, and from then on I was hooked! No clue who originally came up with the idea, and it may be taken from another programming language for all I know, but it’s been around for a long time.

Why Do We Want To Use Self-Healing Object Variables?

When we talk about Object Variables, we are most often talking about the use of the CreateObject() function, or in the case of Access specifically the use of CurrentDb. So things like

Set oExcel = CreateObject("Excel.Application")

Or

Set db= CurrentDb

These actions are straining, especially when done repetitively.  The process of creating ActiveX reference, by using commands like CreateObject(), can cause significant delays, so we want to minimize such activities as much as possible to give the best user experience possible.

The idea behind Self-Healing Variables is you do it once, but hold it in memory that way subsequent calls don’t need to perform the initialization again, as it is still already in memory from the 1st call, ready to go.

Using Self-Healing variables can simplify your code since you no longer need to initialize such variables in your code and can significantly improve overall performance.  On slower machines the performance boost achieved by using self-healing variables can be quite noticeable.

Why Not Use TempVars For This?

Simply put, because you can’t!  TempVars cannot store objects.

What about a Global Variable?

Although, you could technically use a Global Variable to hold an Object Variable, it can get reset and does not offer the same ability that a Self-Healing Variable offers to automatically reinitialize itself.

Don’t misunderstand me though, this technique does indeed use Global variables but in conjunction with a function that determines whether or not to reinitialize the variable or not, that is the key difference!

How Do We Create and Use Self-Healing Object Variables?

Enough talk, let’s dive in!

Below are some simple examples of Self-Healing variables for the most common applications that I use.  You’ll see a pattern, so try and expand it to other programs beyond those listed below.

Also, notice that I created my Self-Healing variables to work both as Early or Late Binding, that why the code isn’t what you’d find elsewhere. You simply change the Conditional Compiler Directive variable (Excel_EarlyBind, Outlook_EarlyBind , …) to switch between the 2. The choice is use. This way, everyone is happy.

So far, I’ve provided 12 examples of Self-Healing variables. These are the ones I consider most common for most developers. Simply click on one of the shortcuts provided below to jump straight to that example:

• Microsoft Access
• Microsoft Excel
• Microsoft Outlook
• Microsoft PowerPoint
• Microsoft Word
• File System Object (FSO)
• Windows Management Instrumentation (WMI)
• Regular Expressions (RegEx)
• Shell
• Script Control
• WScript (WshShell & WshNetwork)
• UIAutomationClient
• HTMLFile/HTMLDocument (Microsoft HTML Object Library – MSHTML)
• ADODB

As you will no doubt see for yourself, they are all very similar in design, so you can easily use any one of them as a template to build others you may need.

Access

From an External Application

If we follow the same pattern as all the other examples we would do:

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Access XX.X Object Library

#Const Access_EarlyBind = True
#If Access_EarlyBind = True Then
    Private pAccess                As Access.Application
#Else
    Private pAccess                As Object
#End If


#If Access_EarlyBind = True Then
Public Function oAccess() As Access.Application
#Else
Public Function oAccess() As Object
#End If
'***** Only work if using full version of Access, NOT Runtime editions! *****
On Error GoTo Err_Handler
    
    If pAccess Is Nothing Then
        Debug.Print "*** Setting oAccess ***"
        #If Access_EarlyBind = True Then
            Set pAccess = New Access.Application
        #Else
            Set pAccess = CreateObject("Access.Application")
        #End If
    End If
    Set oAccess = pAccess

Exit_Procedure:
    DoEvents
    Exit Function

Err_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Function oAccess" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Exit_Procedure
End Function

Public Sub oAccess_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pAccess = Nothing
End Sub

and this does work, in a general sense, but there is a particularity with Access, that is the Runtime Edition! With the Runtime Edition it is impossible to use CreateObject(“Access.Application”) or even GetObject(, “Access.Application”). So the above code is fully functional, but only for full editions of Access. If you need to work with Runtime Edition installations then we would need to modify the code to:

#Const Access_EarlyBind = False
#If Access_EarlyBind = True Then
    Private pAccess                As Access.Application
#Else
    Private pAccess                As Object
#End If


#If Access_EarlyBind = True Then
    Public Function oAccess_RT(ByVal sFile As String) As Access.Application
#Else
    Public Function oAccess_RT(ByVal sFile As String) As Object
#End If
On Error GoTo Err_Handler
    Dim oShell                    As Object
    Dim sDbFile                   As String
    Dim sCmd                      As String
    
    If Not pAccess Is Nothing Then
        On Error GoTo ResetSHOV
        Set pAccess = GetObject(sFile)
    End If
    
    If pAccess Is Nothing Then
        Set pAccess = GetObject(sFile)
        If pAccess Is Nothing Then
ResetSHOV:
            On Error GoTo Err_Handler
            Debug.Print "*** Setting oAccess ***"
            'Workaround for Can't Create error 429 in runtime editions
            sCmd = "msaccess.exe """ & sFile & """"
            Call Shell(sCmd)
            DoEvents
            Set pAccess = GetObject(sFile)
        End If
    End If
    Set oAccess_RT = pAccess

Exit_Procedure:
    Exit Function

Err_Handler:
    If Err.Number = 429 Then
        GoTo ResetSHOV
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Function oAccess_RT" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Exit_Procedure
    End If
End Function

Public Sub oAccessRT_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pAccess = Nothing
End Sub

where we use Shell to start the database if it isn’t already running. And remember, with Runtime you can’t just open/use msaccess.exe, it has to use/open a file. That’s why we have to include a file with the function call.

Within Access Itself – CurrentDb

In Access the most common use of Self-Healing variables would be to replace the use of CurrentDb.  To do so, we create the following:

Private pCurrentDb         As DAO.Database


Public Function oCurrentDb() As DAO.Database
    If pCurrentDb Is Nothing Then
        Debug.Print "*** Setting oCurrentDb ***"
        Set pCurrentDb = CurrentDb
    End If
    Set oCurrentDb = pCurrentDb
End Function

Public Sub oCurrentDb_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pCurrentDb = Nothing
End Sub

and then we can take code such as:

Public Function listTables(Optional bShowSys As Boolean = False) As String
    Dim oCurrentDb                    As DAO.Database
    Dim tdfs                  As DAO.TableDefs
    Dim tdf                   As DAO.TableDef
 
    Set oCurrentDb = CurrentDb()
    Set tdfs = oCurrentDb.TableDefs
    For Each tdf In tdfs    'loop through all the fields of the tables
        If (Left(tdf.Name, 4) = "MSys" Or Left(tdf.Name, 1) = "~") _
            And bShowSys = False Then GoTo Continue
        Debug.Print tdf.Name
Continue:
    Next
 
Exit_Function:
    On Error Resume Next
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not tdfs Is Nothing Then Set tdfs = Nothing
    If Not oCurrentDb Is Nothing Then Set oCurrentDb = Nothing
    Exit Function
End Function

into

Public Function listTables(Optional bShowSys As Boolean = False) As String
    Dim tdfs                  As DAO.TableDefs
    Dim tdf                   As DAO.TableDef
 
    Set tdfs = oCurrentDb.TableDefs
    For Each tdf In tdfs    'loop through all the fields of the tables
        If (Left(tdf.Name, 4) = "MSys" Or Left(tdf.Name, 1) = "~") _
            And bShowSys = False Then GoTo Continue
        Debug.Print tdf.Name
Continue:
    Next
 
Exit_Function:
    On Error Resume Next
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not tdfs Is Nothing Then Set tdfs = Nothing
    Exit Function
End Function

I know it doesn’t seem significant, but it actually is.  The former example will repetitively keep calling CurrentDb each time it is called.  Whereas the latter will only make that initialization call once, store it in the property so every subsequent call simply uses what is already in memory, no need to call CurrentDb again.

Also, notice how we end up with simpler code, no more dimming, setting and clearing the CurrentDb variable.

Think of all the places you use CurrentDb: OpenRecorset, TableDefs, QueryDefs, … and you can simply all that code and give a small boost to each one of those calls.

Excel

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Excel XX.X Object Library

#Const Excel_EarlyBind = False
#If Excel_EarlyBind = True Then
    Private pExcel                As Excel.Application
#Else
    Private pExcel                As Object
#End If


#If Excel_EarlyBind = True Then
Public Function oExcel() As Excel.Application
#Else
Public Function oExcel() As Object
#End If
On Error GoTo Err_Handler
    
    If pExcel Is Nothing Then
        Debug.Print "*** Setting oExcel ***"
        #If Excel_EarlyBind = True Then
            Set pExcel = New Excel.Application
        #Else
            Set pExcel = CreateObject("Excel.Application")
        #End If
    End If
    Set oExcel = pExcel

Exit_Procedure:
    DoEvents
    Exit Function

Err_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Property Get oExcel" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Exit_Procedure
End Function

Public Sub oExcel_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pExcel = Nothing
End Sub

Outlook

PowerPoint

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft PowerPoint XX.X Object Library

#Const PowerPoint_EarlyBind = False
#If PowerPoint_EarlyBind = True Then
    Private pPowerPoint                As PowerPoint.Application
#Else
    Private pPowerPoint                As Object
#End If


#If PowerPoint_EarlyBind = True Then
Public Function oPowerPoint() As PowerPoint.Application
#Else
Public Function oPowerPoint() As Object
#End If
On Error GoTo Err_Handler

    If pPowerPoint Is Nothing Then
        Debug.Print "*** Setting oPowerPoint ***"
        #If PowerPoint_EarlyBind = True Then
            Set pPowerPoint = New PowerPoint.Application
        #Else
            Set pPowerPoint = CreateObject("PowerPoint.Application")
        #End If
    End If
    Set oPowerPoint = pPowerPoint

Exit_Procedure:
    DoEvents
    Exit Function

Err_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Property Get oPowerPoint" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Exit_Procedure
End Function

Public Sub oPowerPoint_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pPowerPoint = Nothing
End Sub

Word

File System Object (FSO)

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Scripting Runtime

#Const FSO_EarlyBind = False
#If FSO_EarlyBind = True Then
    Private pFSO                As Scripting.FileSystemObject
#Else
    Private pFSO                As Object
#End If


#If FSO_EarlyBind = True Then
Public Function oFSO() As Scripting.FileSystemObject
#Else
Public Function oFSO() As Object
#End If
    If pFSO Is Nothing Then
        Debug.Print "*** Setting oFSO ***"
        #If FSO_EarlyBind = True Then
            Set pFSO = New FileSystemObject
        #Else
            Set pFSO = CreateObject("Scripting.FileSystemObject")
        #End If
    End If
    Set oFSO = pFSO
End Function

Public Sub oFSO_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pFSO = Nothing
End Sub

Windows Management Instrumentation (WMI)

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft WMI Scripting VX.X Library

#Const WMI_EarlyBind = False
#If WMI_EarlyBind = True Then
    Private pWMI                As WbemScripting.SWbemServices
#Else
    Private pWMI                As Object
#End If


'Self-healing oWMI property
#If WMI_EarlyBind = True Then
Public Function oWMI(Optional sHost As String = ".") As WbemScripting.SWbemServices
#Else
Public Function oWMI(Optional sHost As String = ".") As Object
#End If
On Error GoTo Err_Handler

    If pWMI Is Nothing Then
        Debug.Print "*** Setting oWMI ***"
        'Set pWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sHost & "\root")
        Set pWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sHost & "\root\cimv2")
    End If
    Set oWMI = pWMI

Exit_Procedure:
    DoEvents
    Exit Function

Err_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Property Get oWMI" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Exit_Procedure
End Function

Public Sub oWMI_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pWMI = Nothing
End Sub

Regular Expressions (RegEx)

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft VBScript Regular Expressions X.X

#Const RegEx_EarlyBind = False
#If RegEx_EarlyBind = True Then
    Private pRegEx                As RegExp
#Else
    Private pRegEx                As Object
#End If


'Self-healing oRegEx property
#If RegEx_EarlyBind = True Then
Public Function oRegEx() As RegExp
#Else
Public Function oRegEx() As Object
#End If
On Error GoTo Err_Handler
    
    If pRegEx Is Nothing Then
        Debug.Print "*** Setting oRegEx ***"
        #If RegEx_EarlyBind = True Then
            Set pRegEx = New RegExp
        #Else
            Set pRegEx = CreateObject("VBScript.RegExp")
        #End If
    End If
    Set oRegEx = pRegEx

Exit_Procedure:
Exit Function

Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
       "Error Number: " & Err.Number & vbCrLf & _
       "Error Source: Property Get oRegEx" & vbCrLf & _
       "Error Description: " & Err.Description & _
       Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
       , vbOKOnly + vbCritical, "An Error has Occured!"
Resume Exit_Procedure
End Function

Public Sub oRegEx_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pRegEx = Nothing
End Sub

Shell

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Shell Controls And Automation

#Const Shell_EarlyBind = False
#If Shell_EarlyBind = True Then
    Private pShell               As Shell32.Shell
#Else
    Private pShell               As Object
#End If


#If Shell_EarlyBind = True Then
Public Function oShell() As Shell32.Shell
#Else
Public Function oShell() As Object
#End If

    If pShell Is Nothing Then
        Debug.Print "*** Setting oShell ***"
        #If Shell_EarlyBind = True Then
            Set pShell = New Shell32.Shell
        #Else
            Set pShell = CreateObject("Shell.Application")
        #End If
    End If
    Set oShell = pShell
End Function

Public Sub oShell_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pShell = Nothing
End Sub

Usage Example

Sub GetPCMemory()
    'Physical memory installed, in bytes.
    Debug.Print oShell.GetSystemInformation("PhysicalMemoryInstalled")
End Sub

Script Control

' Note      : !!!!! -- MSScriptControl does not work in 64-bit Office -- !!!!!
'For 64-bit refer to https://github.com/tablacus/TablacusScriptControl
' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Script Control 1.0

#Const ScriptControl_EarlyBind = False
#If ScriptControl_EarlyBind = True Then
    Private pScriptControl                As MSScriptControl.ScriptControl
#Else
    Private pScriptControl                As Object
#End If


#If ScriptControl_EarlyBind = True Then
Public Function oScriptControl() As MSScriptControl.ScriptControl
#Else
Public Function oScriptControl() As Object
#End If
    
    If pScriptControl Is Nothing Then
        Debug.Print "*** Setting oScriptControl ***"
        #If ScriptControl_EarlyBind = True Then
            Set pScriptControl = New ScriptControl
        #Else
            Set pScriptControl = CreateObject("MSScriptControl.ScriptControl")
        #End If
    End If
    Set oScriptControl = pScriptControl
End Function

Public Sub oScriptControl_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pScriptControl = Nothing
End Sub

WScript

WScript.Shell and WScript.Network

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Windows Script Host Object Model

#Const WSH_EarlyBind = False
#If WSH_EarlyBind = True Then
    Private pWshShell               As IWshRuntimeLibrary.WshShell
    Private pWshNetwork             As IWshRuntimeLibrary.WshNetwork
#Else
    Private pWshShell               As Object
    Private pWshNetwork             As Object
#End If


#If WSH_EarlyBind = True Then
Public Function oWshShell() As IWshRuntimeLibrary.WshShell
#Else
Public Function oWshShell() As Object
#End If

    If pWshShell Is Nothing Then
        Debug.Print "*** Setting oWshShell ***"
        #If WSH_EarlyBind = True Then
            Set pWshShell = New IWshRuntimeLibrary.WshShell
        #Else
            Set pWshShell = CreateObject("WScript.Shell")
        #End If
    End If
    Set oWshShell = pWshShell
End Function

Public Sub oWshShell_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pWshShell = Nothing
End Sub

#If WSH_EarlyBind = True Then
Public Function oWshNetwork() As IWshRuntimeLibrary.WshNetwork
#Else
Public Function oWshNetwork() As Object
#End If


    
    If pWshNetwork Is Nothing Then
        Debug.Print "*** Setting oWshNetwork ***"
        #If WSH_EarlyBind = True Then
            Set pWshNetwork = New IWshRuntimeLibrary.WshNetwork
        #Else
            Set pWshNetwork = CreateObject("WScript.Network")
        #End If
    End If
    Set oWshNetwork = pWshNetwork
End Function

Public Sub oWshNetwork_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pWshNetwork = Nothing
End Sub

UIAutomationClient

Please note that it is not possible to perform Late Binding with the UIAutomationClient library so you must add the UIAutomationClient library to your VBA References.

Private pUIA As UIAutomationClient.CUIAutomation


Public Function oUIA() As UIAutomationClient.CUIAutomation
    If pUIA Is Nothing Then
        Set pUIA = New UIAutomationClient.CUIAutomation
    End If
    Set oUIA = pUIA
End Function

Public Sub oUIA_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pUIA = Nothing
End Sub

HTMLFile/HTMLDocument (Microsoft HTML Object Library – MSHTML)

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft HTML Object Library
#Const HTMLFile_EarlyBind = False
#If HTMLFile_EarlyBind = True Then
    Private pHTMLFile         As MSHTML.HTMLDocument
#Else
    Private pHTMLFile         As Object
#End If


#If HTMLFile_EarlyBind = True Then
Public Function oHTMLFile(Optional bForceRefresh As Boolean = False) As MSHTML.HTMLDocument
#Else
Public Function oHTMLFile(Optional bForceRefresh As Boolean = False) As Object
#End If
    If pHTMLFile Is Nothing Or bForceRefresh Then
        Debug.Print "*** Setting oHTMLFile ***"
        #If HTMLFile_EarlyBind = True Then
            Set pHTMLFile = New MSHTML.HTMLDocument
        #Else
            Set pHTMLFile = CreateObject("HTMLFile")
        #End If
    End If
    Set oHTMLFile = pHTMLFile
End Function

Public Sub oHTMLFile_Clear()
'Be sure to always run this when closing your Form/DB to avoid
'   hidden instances from running in the background!
    Set pHTMLFile = Nothing
End Sub

ADODB

ADODB Stream

' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft ActiveX Data Objects x.x Library

#Const ADODBStream_EarlyBind = False
#If ADODBStream_EarlyBind = True Then
    Private pADODBStream                As ADODB.stream
#Else
    Private pADODBStream                As Object
#End If


'Self-healing oADODBStream property
#If ADODBStream_EarlyBind = True Then
Public Function oADODBStream() As ADODB.stream
#Else
Public Function oADODBStream() As Object
#End If
On Error GoTo Err_Handler

If pADODBStream Is Nothing Then
    Debug.Print "*** Setting oADODBStream ***"
    #If ADODBStream_EarlyBind = True Then
        Set pADODBStream = New ADODB.stream
    #Else
        Set pADODBStream = CreateObject("ADODB.Stream")
    #End If
End If
Set oADODBStream = pADODBStream

Exit_Procedure:
Exit Function

Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
       "Error Number: " & Err.Number & vbCrLf & _
       "Error Source: Property Get oADODBStream" & vbCrLf & _
       "Error Description: " & Err.Description & _
       Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
       , vbOKOnly + vbCritical, "An Error has Occured!"
Resume Exit_Procedure
End Function

Public Sub oADODBStream_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pADODBStream = Nothing
End Sub

Usage Example

'---------------------------------------------------------------------------------------
' Procedure : ReadFileAsText
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : This code opens a UTF-8 encoded text file and reads its entire contents
'             ***** This version employs SHOV *****
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft ActiveX Data Objects x.x Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to read
'
' Usage:
' ~~~~~~
' ReadFileAsText("C:\Users\Dev\Desktop\Booking.json")
'   Returns -> the content of the file in question
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2012-05-14
' 2         2025-04-21              Initial Public Release
'---------------------------------------------------------------------------------------
Function ReadFileAsText(ByVal sFile As String) As String
    On Error GoTo Error_Handler

    With oADODBStream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile sFile
        ReadFileAsText = .ReadText
        .Close
    End With

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ReadFileAsText" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Cleaning Up After Ourselves

Remember to cleanup up the Self-Healing Object Variables when closing the database, the use of a hidden form is great for this, by simply running the appropriate *_Clear() sub to ensure we don’t leave behind any orphaned hidden processes running in the background.

Speed Comparison

As I have stated SHOV simplify coding and speed up process. Some have questioned such a statement, so I did a little experiment. I set out to use a couple different techniques to see if a file existed.

I used:

• Dir
• FSO Early Binding
• FSO Late Binding
• SHOV FSO Early Binding
• SHOV FSO Late Binding

Then I build a test that performed 1000 iterations, logged the results, averaged the values and we end up with the following results:

So, both SHOV approaches come out on top!

Using traditional approaches will always be slower if more than one call is done within your database.

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

Feel free to download a 100% unlocked copy by using the link provided below:

A Few Resources on the Subject

CreateObject function (Visual Basic for Applications)

Creates and returns a reference to an ActiveX object.