checking to see if a table is open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to write some code that will check to see if a table is open and
if it is I want to close that table.

Is there an If statement I can use to do this. Thanks for the help......
 
JT:
I'm trying to write some code that will check to see if a table is
open and if it is I want to close that table.

Is there an If statement I can use to do this. Thanks for the
help......
Click to expand...

Public Function IsTableOpen(strName As String) As Boolean
IsTableOpen = SysCmd(acSysCmdGetObjectState, acTable, strName)
End Function

If IsTableOpen("tblEmployees") Then
....


Acki
 
In addition to what Joerg posted:

Sub AllTables()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
' Print name of obj.
Debug.Print obj.Name
End If
Next obj
End Sub

The code above iterates through all tables and lists those that are loaded.

Randall Arnold
 
Yes, Joerg's method will work for any Access object.

Yes, as long as you replace "acTable" for each object type. Alternatively,
you could use a more generic IsOpen routine and pass the object name *and*
it's type.

Public Function IsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True (non-zero) if strObjName is Open, False(0) if not Open or
doesn't exist.
' (subform status can't be tested this way)
'
' Legal acObjectTypes include: acForm (default), acTable, acQuery,
acReport (see ObjectBrowser for complete list)

On Error Resume Next
IsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <> 0)
End Function

If IsLoaded("tblEmployees", acTable) Then


HTH,
 
Back
Top