Determine if table is open in VBA and if open close that table saving changes

In Access 2003 VBA, I would like to determine if a table has been opened manually, if it has been opened I would like to save the table and close it all via VBA.
LVL 11
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
I am not aware of any way to tell if a table has been opened 'manually' - if you mean say be a user ?

But you could close any open tables like so:

docmd.Close acTable,"Table1",acSavePrompt

Any table? What do you mean by 'opened manually'?
omgangConnect With a Mentor IT ManagerCommented:
Function isOpen(strName As String, intObjectType As Integer) As Integer
'checks to see if the database object passed is open or not
'returns true if object is open
    isOpen = (SysCmd(SYSCMD_GETOBJECTSTATE, intObjectType, strName) <> 0)
End Function

Public Function CloseObject(strName As String)

    DoCmd.Close acTable, strName, acSaveYes
End Function

Object types
acTable  0  
acQuery  1  
acForm  2  
acReport  3  
acMacro  4  
acModule  5  
acDataAccessPage  6  

OM Gang
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Note that the acSavePrompt is referring to Design changes, not data changes - which would probably get saved when the table closes anyway.

omgangIT ManagerCommented:
mx is correct, the acSaveYes pertains to design changes.
OM Gang
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would suggest that, if your users can directly access your tables, then the least of your worries would be whether a table is "manually open" or not ...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
good one omg ... forgot about SysCmd !

thydzikAuthor Commented:
thanks for the quick answers
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.