Obtain Access Information/State of an Object

[ADezii]

In Tip #12, we showed how to display a Progress Meter within Access by using the SysCmd() Function. By using this same Function, with different Action Arguments, we will now demonstrate how you can return specific Access information as well as determining an Object's (Table, Form, Report, Macro, Module, etc) State. Instead of preceding the code display with an Overview, I decided to more fully comment the code, since for the most part it will be self explanatory. Sample output will then be shown to demonstrate the code's end result.

1. To Retrieve Access Specific Information:
   

   Debug.Print "1) The Main Access Directory is: " & SysCmd(acSysCmdAccessDir)
   Debug.Print "2) The Access Version is: " & SysCmd(acSysCmdAccessVer)
   Debug.Print "3) The Path to the Access Woekgroup File is: " & SysCmd(acSysCmdGetWorkgroupFile)
   Debug.Print "4) The Name of the .ini File associated with Access is: " & SysCmd(acSysCmdIniFile)
   Debug.Print "5) Is this Version of Access the Runtime Version?: " & IIf(SysCmd(acSysCmdRuntime), "Yes", "No")
   Debug.Print "6) The /profile setting specified by the user when starting Microsoft Access from the command line: " & IIf(IsNull(SysCmd(acSysCmdProfile)), "None Specified", SysCmd(acSysCmdProfile))

   OUTPUT
   

    The Main Access Directory is: C:\Program Files\Microsoft Office\Office\
   The Access Version is: 9.0
   The Path to the Access Woekgroup File is: C:\PROGRA~1\MICROS~2\Office\SYSTEM.MDW
   The Name of the .ini File associated with Access is: msacc30.ini
   Is this Version of Access the Runtime Version?: No
   The /profile setting specified by the user when starting Microsoft Access from the command line: None Specified

   ------------------------------------------
2. To Determine the State of an Access Object:
   

   ObjectState = SysCmd(action[, objecttype][, objectname])
   'objecttype can be 1 of the following:
   'acTable, acQuery, acForm, acReport, acMacro,
   'acModule, acDataAccessPage, acDefault, 
   'acDiagram, acServerView, acStoreProcedure
    
   'An Object can be in one of four possible states or variations thereof:
   'return Value of 0 = not open or non-existent
   'acObjStateOpen = 1
   'acObjStateDirty = 2 (Changed but not saved )
   'return Value of 3 = Open and Dirty
   'acObjStateNew = 4
   'return Value of 5 = Open and New
   'return Value of 7 = Open, Dirty, and New
    
   Debug.Print "State of Form3: " & SysCmd(acSysCmdGetObjectState, acForm, "Form3")
   Debug.Print "State of rptFinance: " & SysCmd(acSysCmdGetObjectState, acReport, "rptFinance")
   Debug.Print "State of tblEmployee: " & SysCmd(acSysCmdGetObjectState, acTable, "tblEmployees")
   Debug.Print "State of qryCustomers: " & SysCmd(acSysCmdGetObjectState, acQuery, "qryCustomers")
   Debug.Print "State of mcrOpenDatabase: " & SysCmd(acSysCmdGetObjectState, acMacro, "mcrOpenDatabase")
   Debug.Print "State of mdlUtilities: " & SysCmd(acSysCmdGetObjectState, acModule, "mdlUtilities")

3. OUTPUT
   

   State of Form3: 1 (Open)
   State of rptFinance: 0 (Not Open or Non-Existent)
   State of tblEmployee: 5 (Open and New)
   State of qryCustomers: 3 (Open and Dirty)
   State of mcrOpenDatabase: 7 (Open, Dirty, and New)
   State of mdlUtilities: 0] (Not Open or Non-Existent)