Closing tables etc using VBA

[jack1080]

I wish to detect is there any opened tables, queries, forms, reports, pages(item that may potentially locked the table, and close them automatically using VBA. How to do this?

 

[lameid]

There is not a practical way. Could you present the problem that is leading to this solution and perhaps there is an alternative?

Otherwise, I am thinking that you would have to step through the forms and reports collections, check their recordsources recursively for the table and if so close them. That is just one piece of the puzzle. The other collections that have the objects in them they do not indicate that they are open so off the top of my head I don't have any initial thoughts.

 

[CautionMP]

jack1080,
One practical way in a single user environmet would be to enumerate through the collections of AccessObjects, check if they are loaded, and if they are close them.

Sub CloseOpenObjects()
Dim aobCurrent As AccessObject
For Each aobCurrent In [b]CurrentData[/b].[red]AllTables[/red]
  If aobCurrent.IsLoaded Then
    'The [i]ObjectType[/i] argument of the Close action needs to
    'change depending on the Access Object Collection your
    'working with
    DoCmd.Close [red]acTable[/red], aobCurrent.Name
  End If
Next aobCurrent

For Each aobCurrent In CurrentData.[blue]AllQueries[/blue]
  If aobCurrent.IsLoaded Then
    DoCmd.Close [blue]acQuery[/blue], aobCurrent.Name
  End If
Next aobCurrent

'Notice the change from CurrentData to CurrentProject when the code moves
'from Data containers to UI components
For Each aobCurrent In [b]CurrentProject[/b].[green]AllForms[/green]
  If aobCurrent.IsLoaded Then
    DoCmd.Close [green]acForm[/green], aobCurrent.Name
  End If
Next aobCurrent

'...AllPages
'...AllReports
'...
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jack1080]

Wow, this is very helpful, thanks
1)But how about pages, is it allpages?
2)In a single user environment, other than opened tables, queries, forms, reports, pages, does there exist another object that may possibly locked the tables?

 

[CautionMP]

jack1080,
I think it is AllPages. When your typing the code you should get the 'type ahead' options when you put the period after [tt]CurrentProject[/tt].

Yes it's possible, but not very likely. As an example if you had a global recordset object that was initialized in a routine somewhere, so it's open but not being used...

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)