List All Database Objects using VBA 3

[jazminecat]

I've created a button to list all the items in the database, and managed to make it list the queries. But I don't know how to then make it list the other items.

This works:

Private Sub cmdListAll_Click()

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllQueries collection.
For Each obj In dbs.AllQueries
' Print name of obj.
Debug.Print obj.Name
Next obj


But then when I tried this it crashes:


Private Sub cmdListAll_Click()

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllQueries collection.
For Each obj In dbs.AllQueries
' Print name of obj.
Debug.Print obj.Name
Next obj

For Each obj In dbs.AllForms
Debug.Print obj.Name
Next obj


Do i need to use a With instead? And if so, how do it string them all together such that I can click this one button and list all of the database objects in the debug window?

Thanks!

 

[zevw]

for forms

Dim frm As Form, frmName As String


' Enumerate Forms collection.
For Each frm In Forms
' Print name of form.
frmName = frm.Name
' Enumerate Controls collection of each form.
Next frm

 

[BillPower]

Hi Tables and Queries use the CurrentData property, Forms, Reports, Macros and Modules use the CurrentProject property:

Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData
Debug.Print "Tables:"
For Each obj In dbs.AllTables
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentData
Debug.Print "Queries:"
For Each obj In dbs.AllQueries
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Forms:"
For Each obj In dbs.AllForms
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Reports:"
For Each obj In dbs.AllReports
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Macros:"
For Each obj In dbs.AllMacros
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Modules:"
For Each obj In dbs.AllModules
Debug.Print " " & obj.Name
Next obj

Bill

 

[jazminecat]

Star for you Bill!

Thanks a million - this works like a charm, and now I understand a lot better how to use multiple For Each..Next statements in one subroutine. Thanks again!

 

[zevw]

Thanks Bill!

 

[MichaelRed]

but you need to rea;ize that these only list the 'top level' objects. Each control/field is alos an 'object'. Some of these objects also inlclude 'objects' (e.g. a control may have a query as one of it's "properties" (ControlSource or RowSource in particular) and the query is NOT listed in the "AllQueries" collection. So, at least in a sense, you are getting all of the objects - but from another perspective you are getting only the objects included in the specific collections.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

Bill,

Your post is definitely worth a star!

Question: When I tested the code as a subprocedure, it bombed on queries. Made sense since I was testing inside an Access Project - no queries available!

So, is there a way to programmatically determine whether the database is plain Access or Access Project?
The code could then figure out whether to look for queries.

Which leads to question 2:
What about Views and Stored Procedures?

TIA!
Bob

 

[BillPower]

Hi Bob,

This should do it for you.

Dim obj As AccessObject, dbs As Object, dbType As String
dbType = Right(Application.CurrentProject.Name, 3)

If dbType = "mdb" Or dbType = "adp" Then
Set dbs = Application.CurrentData
Debug.Print "Tables:"
For Each obj In dbs.AllTables
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Forms:"
For Each obj In dbs.AllForms
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Reports:"
For Each obj In dbs.AllReports
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Macros:"
For Each obj In dbs.AllMacros
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentProject
Debug.Print "Modules:"
For Each obj In dbs.AllModules
Debug.Print " " & obj.Name
Next obj
End If

If dbType = "mdb" Then
Set dbs = Application.CurrentData
Debug.Print "Queries:"
For Each obj In dbs.AllQueries
Debug.Print " " & obj.Name
Next obj
End If

If dbType = "adp" Then
Set dbs = Application.CurrentData
Debug.Print "Stored Procedures:"
For Each obj In dbs.AllStoredProcedures
Debug.Print " " & obj.Name
Next obj

Set dbs = Application.CurrentData
Debug.Print "Views:"
For Each obj In dbs.AllViews
Debug.Print " " & obj.Name
Next obj
End If
Set dbs = Nothing

Thanks for the star.

To anyone reading this that is waiting on an answer from me. Sorry, I am trying to catch up and you should be hearing from me very soon.

Bill