Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB

[SachAccess]

Hi,
Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB
What I am trying to do is, when I run this macro, it should give me list of all the queries, reports, forms, macros, modules from the DB.
May be it can create a table in DB itself or create a list in Excel, both is fine.
If anyone has such ready code, could you please help me.

 

[SHANEMAC51]

Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB

Sub mreport()
''my first, simplest code'''''''''''''''''''''''''''
''drawback: tables and queries in the same container
''therefore, then I skipped 3 containers
''1- containers for forms/reports/modules/macros
''2 -tabledefs for tables
''3 -querydefs for queries
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim cont As DAO.Container
Set dbs = CurrentDb
For Each cont In dbs.Containers
Debug.Print "Container:"; cont.Name
For Each doc In cont.Documents
If doc.Name Like "msys*" Then
Else
Debug.Print doc.LastUpdated, doc.Name
End If
Next doc
Next cont
End Sub

 

[MajP]

Listing of Database Objects (Tables, Queries, Forms, Reports, ...)

One approach using a simple query to get a listing of various database object types (Tables, Queries, Forms, Reports, Macros, Modules)

www.devhut.net

 

[GPGeorge]

Check out this free download.

 

[SHANEMAC51]

return name of all the Queries, Reports, Form and Modules from the DB

var2-mod

Sub mreport()
''my first, simplest code'''''''''''''''''''''''''''
''drawback: tables and queries in the same container
''therefore, then I skipped 3 containers
''1- containers for forms/reports/modules/macros
''2 -tabledefs for tables
''3 -querydefs for queries
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim cont As DAO.Container

Set dbs = CurrentDb
For Each cont In dbs.Containers
Debug.Print "Container:"; cont.Name, "---------------"
For Each doc In cont.Documents
If doc.Name Like "msys*" Then
Else
Debug.Print doc.LastUpdated, doc.Name
End If
Next doc
Next cont
''''''''''''''''''
Dim tbl As DAO.TableDef
Dim que As QueryDef
''''''''''''''''''
Debug.Print "TableDefs:-------------------------"
For Each tbl In dbs.TableDefs
If tbl.Name Like "msys*" Then
Else
Debug.Print "~~"; tbl.Name, tbl.Connect & ""
End If
Next tbl
''''''''''''''''''
Debug.Print "queryDefs:-------------------------"
For Each que In dbs.QueryDefs
If que.Name Like "~*" Then
Else
Debug.Print "~~"; que.Name, "-------------------------"
Debug.Print que.SQL
End If
Next que
End Sub

 

[SachAccess]

var2-mod

Sub mreport()
''my first, simplest code'''''''''''''''''''''''''''
''drawback: tables and queries in the same container
''therefore, then I skipped 3 containers
''1- containers for forms/reports/modules/macros
''2 -tabledefs for tables
''3 -querydefs for queries
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim cont As DAO.Container

Set dbs = CurrentDb
For Each cont In dbs.Containers
Debug.Print "Container:"; cont.Name, "---------------"
For Each doc In cont.Documents
If doc.Name Like "msys*" Then
Else
Debug.Print doc.LastUpdated, doc.Name
End If
Next doc
Next cont
''''''''''''''''''
Dim tbl As DAO.TableDef
Dim que As QueryDef
''''''''''''''''''
Debug.Print "TableDefs:-------------------------"
For Each tbl In dbs.TableDefs
If tbl.Name Like "msys*" Then
Else
Debug.Print "~~"; tbl.Name, tbl.Connect & ""
End If
Next tbl
''''''''''''''''''
Debug.Print "queryDefs:-------------------------"
For Each que In dbs.QueryDefs
If que.Name Like "~*" Then
Else
Debug.Print "~~"; que.Name, "-------------------------"
Debug.Print que.SQL
End If
Next que
End Sub

Thanks a lot @SHANEMAC51 , this is awesome! Have a nice day ahead.

 

[SachAccess]

H

Listing of Database Objects (Tables, Queries, Forms, Reports, ...)

One approach using a simple query to get a listing of various database object types (Tables, Queries, Forms, Reports, Macros, Modules)

www.devhut.net

Listing of Database Objects (Tables, Queries, Forms, Reports, ...)

One approach using a simple query to get a listing of various database object types (Tables, Queries, Forms, Reports, Macros, Modules)

www.devhut.net

Hi @MajP , thanks a lot for the help. Have a nice day ahead.

 

[isladogs]

Looks like I'm a bit late but you can have a look at my version

View Database Objects & Metadata

This article includes an example app used to view all objects in a database together with their properties and metadata.

www.isladogs.co.uk

No need to save the list to a table as the info comes from a system table and is always up to date

 

[SachAccess]

Looks like I'm a bit late but you can have a look at my version

View Database Objects & Metadata

This article includes an example app used to view all objects in a database together with their properties and metadata.

www.isladogs.co.uk

No need to save the list to a table as the info comes from a system table and is always up to date

Hi @isladogs thanks a lot for the help. You are helping me from your busy time, so never late.
Will download on personal system and confirm. Have a nice day ahead.

 

[SachAccess]

Sub mreport()
''my first, simplest code'''''''''''''''''''''''''''
''drawback: tables and queries in the same container
''therefore, then I skipped 3 containers
''1- containers for forms/reports/modules/macros
''2 -tabledefs for tables
''3 -querydefs for queries
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim cont As DAO.Container
Set dbs = CurrentDb
For Each cont In dbs.Containers
Debug.Print "Container:"; cont.Name
For Each doc In cont.Documents
If doc.Name Like "msys*" Then
Else
Debug.Print doc.LastUpdated, doc.Name
End If
Next doc
Next cont
End Sub

Hi @SHANEMAC51 this code is perfectly working for me. No issues. Just wanted to know couple of things if you get time.


'ABC_Random_Text_Update', this is a query name. Code returns this value in print window.
However is there any way to get object type as well from this code. Like, if this is a query or report in that sence.
I tried editing 'doc.Properties' but was not successful.
Kindly pardon my ignorance, am new to Access.

Also, what are the objects starting with '~sq_.........'.
This is not urgent for me, you may help if you have time.

 

[arnelgp]

Also, what are the objects starting with '~sq_..

i think that is Deleted query, will be deleted later by msa.

 

[theDBguy]

Also, what are the objects starting with '~sq_.........'.

Sometimes, it could be the Row Source for a combo or listbox.

 

[isladogs]

i think that is Deleted query, will be deleted later by msa.

No. Deleted items are renamed with a ~TMPCLP prefix

Items beginning ~sq_ are row sources for forms/reports & their combos & listboxes.
These are treated as temp queries by Access (Type = 5, Flags=3) to prevent them appearing in the navigation pane.
It is best to exclude these from any code used to list database objects

For further info, see the Wrongly Flagged items section of my article:

Remove deleted objects

This article explains how to remove 'legacy' deleted objects from the read only MSysObjects system table if you have problems.

www.isladogs.co.uk

 

[Pat Hartman]

Here's an example that uses MSysObjects. Just copy and paste this into SQL view of the QBE. Save and run.

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.DateUpdate, MSysObjects.DateCreate
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=5 Or (MSysObjects.Type)=6 Or (MSysObjects.Type)=-32761 Or (MSysObjects.Type)=-32764 Or (MSysObjects.Type)=-32766 Or (MSysObjects.Type)=-32768) AND ((Left([Name],4))<>"MSys"))
ORDER BY MSysObjects.DateUpdate DESC;

If you want to get a little fancier and show a name for the Type, left join to the table below.

Type    TypeDesc
-32768    Form
-32766    Macro
-32764    Reports
-32761    Module
-32758    Users
-32757    Database Document
-32756    Data Access Pages
1    Table - Local Access Tables
2    Access Object - Database
3    Access Object - Containers
4    Table - Linked ODBC Tables
5    Queries
6    Table - Linked Tables or Files
8    Relationships
9    Constraints

I say to use a left join because Access adds new objects and my list may not include all objects. if I haven't created an object in my database, I won't have seen what the Type code is. If you do the join, you might want to remove the criteria from the query above and that will show all the objects in your database, not just the ones you asked for. This will be much more important if you don't use a consistent prefix for your object names.

SELECT MSysObjects.Name, MSysObjects.Type, tblObjectTypeCodes.TypeDesc, MSysObjects.DateUpdate, MSysObjects.DateCreate
FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=5 Or (MSysObjects.Type)=6 Or (MSysObjects.Type)=-32761 Or (MSysObjects.Type)=-32764 Or (MSysObjects.Type)=-32766 Or (MSysObjects.Type)=-32768) AND ((Left([Name],4))<>"MSys"))
ORDER BY MSysObjects.DateUpdate DESC;

 

[Pat Hartman]

@SHANEMAC51
1. Please format large blocks of code when you paste them. They are much easier to read.
2. Do not bypass query objects that start with "~" Those are temp querydefs Access creates when you use SQL strings in the RecordSource or RowSource rather than a table name or querydef name. I tend to not ignore these at all because access removes deleted objects when you compact. I haven't looked ingo the use of ~ in other collections so I don't know if they only apply to deleted objects or if there is some other use as there is with querydefs.

 

[SHANEMAC51]

it is extremely rare for me to have blocks of text larger than 10 lines, usually I separate sub-blocks of text with a line of apostrophes with a comment (assignment) of the block

I use margins, but no more than 2 per subblock - for me it is more important that the entire code fits into the width of the screen, even by changing the indentation from 4 characters to 2

I often use GOTO to exit the subblock finally cycle

 

[Pat Hartman]

I was talking about the alignment. All your code is left aligned. That makes it hard to identify dependent code.

The important point was the usage of the "~" for temporary querydefs created by Access when you embed SQL in RecordSource and RowSource properties. Did you understand that?

 

[arnelgp]

Items beginning ~sq_ are row sources for forms/reports & their combos & listboxes.

yes, i believe they are and i believe this more than anything, written 15 years ago, i am not even using MSA then:
MSysObjects – Queries lists that start with ~sq_ | Microsoft Office Forums (office-forums.com)

 

[Pat Hartman]

In earlier versions of Access, Access created these temp queries EVERY time they were used. That is one of the behaviors that caused bloat in the past because in order to use the temp querydef, Access would also need to create an execution plan. At some point, possibly with A2007, Access made these "temp" querydefs permanent. So they persist and their execution plans are not recreated each time they are used. So now it doesn't matter whether you use an SQL string as a RecordSource or a querydef although I still use querydefs because there is a bug that sometimes corrupts the SQL String and it always happens to the most complex queries. This change stopped a lot of bloat. Access left the ~ prefix to avoid confusing the user by creating permanently querydefs that would show up in the Nav Pane. So technically they are still "temp" because they belong to Access rather than us.

 

[SachAccess]

In earlier versions of Access, Access created these temp queries EVERY time they were used. That is one of the behaviors that caused bloat in the past because in order to use the temp querydef, Access would also need to create an execution plan. At some point, possibly with A2007, Access made these "temp" querydefs permanent. So they persist and their execution plans are not recreated each time they are used. So now it doesn't matter whether you use an SQL string as a RecordSource or a querydef although I still use querydefs because there is a bug that sometimes corrupts the SQL String and it always happens to the most complex queries. This change stopped a lot of bloat. Access left the ~ prefix to avoid confusing the user by creating permanently querydefs that would show up in the Nav Pane. So technically they are still "temp" because they belong to Access rather than us.

Thanks a lot. Have a nice weekend.