How to check for open Access objects

VBA code that refers to an open Access object will fail if the object isn't open. With good error handling you can capture the error and work around it. A better solution is to make sure the object is open before you reference it. That way you avoid the error altogether.

You don't need anything fancy — a short, generic user-defined function will do. Simply call IsOpen() as needed:

Function IsOpen(strname As String, strtype As String) As Boolean

If SysCmd(acSysCmdGetObjectState, strtype, strname) <> 0 Then

IsOpen = True

End If

End Function

Pass this function the name of the object you're checking and its type (which follow):

Table acTable 0
Query acQuery 1
Form acForm 2
Report acReport 3
Macro acMacro 4
Module acModule 5
Data Access Page acDataAccessPage 6

When an object is closed or doesn't exist, SysCmd() returns 0. Otherwise, it returns the following values:

Open acObjStateOpen 1
Changed but not saved acObjStateDirty 2
New acObjStateNew 4

For the purpose of determining if the object is open, the function need only know that the object's current state doesn't equal 0. This function should work in version 2000 through 2007.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks