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

SachAccess

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
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

Active member
Local time
Today, 08:36
Joined
Jan 28, 2022
Messages
310
Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB
Code:
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
 

SHANEMAC51

Active member
Local time
Today, 08:36
Joined
Jan 28, 2022
Messages
310
return name of all the Queries, Reports, Form and Modules from the DB
var2-mod
Code:
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

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
var2-mod
Code:
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

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
H

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

isladogs

MVP / VIP
Local time
Today, 05:36
Joined
Jan 14, 2017
Messages
18,164
Looks like I'm a bit late but you can have a look at my version

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

SachAccess

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
Looks like I'm a bit late but you can have a look at my version

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

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
Code:
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.
 

isladogs

MVP / VIP
Local time
Today, 05:36
Joined
Jan 14, 2017
Messages
18,164
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:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:36
Joined
Feb 19, 2002
Messages
42,872
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.

Code:
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;
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:36
Joined
Feb 19, 2002
Messages
42,872
@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.
 
Last edited:

SHANEMAC51

Active member
Local time
Today, 08:36
Joined
Jan 28, 2022
Messages
310
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

Super Moderator
Staff member
Local time
Today, 01:36
Joined
Feb 19, 2002
Messages
42,872
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:36
Joined
Feb 19, 2002
Messages
42,872
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

Active member
Local time
Today, 11:06
Joined
Nov 22, 2021
Messages
389
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. :)
 

Users who are viewing this thread

Top Bottom