checking to see if a table is open

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JT - 20 Sep 2005 21:23 GMT

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......

Signature

Reply to this Message

Joerg Ackermann - 20 Sep 2005 21:43 GMT

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......

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

If IsTableOpen("tblEmployees") Then
...

Acki

Reply to this Message

JT - 20 Sep 2005 21:57 GMT

Thanks for the help........does this work for a form as well?

Signature

> JT:
>
[quoted text clipped - 12 lines]
>
> Acki

Reply to this Message

Randall Arnold - 20 Sep 2005 22:28 GMT

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

Randall Arnold

> Thanks for the help........does this work for a form as well?
>
[quoted text clipped - 14 lines]
> >
> > Acki

Reply to this Message

George Nicholson - 21 Sep 2005 00:43 GMT

> 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,

Signature

George Nicholson

Remove 'Junk' from return address.

> Yes, Joerg's method will work for any Access object.
>
[quoted text clipped - 18 lines]
>> >
>> > Acki

Reply to this Message

Randall Arnold - 20 Sep 2005 21:57 GMT

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

> 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......

Reply to this Message