MSAccess VBA - Looping all objects in MSAccess Database

Question

Dear All,

from one MSAccess database I would like to remove all the Tables, Queries, Forms, Macros, Reports I have in an MSAccess database placed in a different location.

I know by a for each loop I can remove Tables and Queries but I need the same loop for Forms, Macros, Reports.

Any way loop through the all objects (Tables, Queries, Forms, Macros, Reports) in an MSAccess database?

I found this code:

Private Sub cmdOK_Click() 

    Dim Obj As AccessObject 

    Dim Dbs As Object 

    Set Dbs = OpenDatabase("D:\Folder\Test.accdb", False, False, ";pwd=" & DatabasePassword & "") 

    For Each Obj In Dbs.AllQueries 

        Debug.Print Obj.Name 

    Next Obj 

End Sub

but I get this error on this line For Each Obj In Dbs.AllQueries

Any suggestion?

Thanks

Answer 1

Dbs is a DAO,Database object.

AllQueries is not a property of the DAO Database object, but of Access.CurrentData.

Use the QueryDefs collection of the DAO.Database object.

Private Sub cmdOK_Click() 
    Dim Qdf As Object 
    Dim Dbs As Object 
    Set Dbs = OpenDatabase("D:\Folder\Test.accdb", False, False, ";pwd=" & DatabasePassword & "") 
    For Each Qdf In Dbs.QueryDefs
        Debug.Print Qdf.Name 
    Next Qdf
End Sub