MS Access - open objects problem

[47david]

Hi All
Question re MS Access 2002 for Win XP
new to this forum but have been following as a 'viewer' for some occasional help
now have one I cant find any answers to

I want to be able to identify/find any open or active queries, OR alternately tell if a particular table is 'in use' or locked in any way before trying to do a remotely controlled 'rename'

I have tried everything I can see with MsysObjects, MsysQueries, etc but can only get lists of ALL tables or ALL Queries etc - can not see any way to filter it down to open or active or locked

[seandean]

I hope this helps.

Function LoadedTablesQueries(TableQueryName As String)
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
 
    ' Set two loops, one for Tables and one for Queries
 
    For Each obj In dbs.AllTables
        If obj.IsLoaded = True Then
        If obj.Name = TableQueryName Then LoadedTablesQueries = True
        Else
        LoadedTablesQueries = False
        End If
    Next obj
 
 
    For Each obj In dbs.AllQueries
        If obj.IsLoaded = True Then
        If obj.Name = TableQueryName Then
        LoadedTablesQueries = True
        Else
        LoadedTablesQueries = False
        End If
    Next obj
 
 
 
 
End Function

[47david]

I hope this helps.

Yep - you got it in one - that's perfect

I dont know much about VB so the discovery of some of those collections and properties is really exciting

I can put in some detail checking and force a close on something that might cause me some grief using DoCmd.Close

Thanks for your time and especially for the code

David