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

S

SachAccess

Active member
Local time
Tomorrow, 00:15
Joined
Nov 22, 2021
Messages
428
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.
 
SachAccess said:
Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB
Click to expand...
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
 
SachAccess said:
return name of all the Queries, Reports, Form and Modules from the DB
Click to expand...
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
 
SHANEMAC51 said:
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
Click to expand...
Thanks a lot @SHANEMAC51 , this is awesome! Have a nice day ahead. :)
 
H
MajP said:

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
Click to expand...

MajP said:

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
Click to expand...
Hi @MajP , thanks a lot for the help. Have a nice day ahead. :)
 
Looks like I'm a bit late but you can have a look at my version
www.isladogs.co.uk

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 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
 
isladogs said:
Looks like I'm a bit late but you can have a look at my version
www.isladogs.co.uk

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 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
Click to expand...
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.
 
SHANEMAC51 said:
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
Click to expand...
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 said:
i think that is Deleted query, will be deleted later by msa.
Click to expand...
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:
www.isladogs.co.uk

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 www.isladogs.co.uk
 
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 said:
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.
Click to expand...
Thanks a lot. Have a nice weekend. :)
 
SachAccess said:
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.
Click to expand...
SELECT * FROM mSysObjects
 
No need to store the data in a table and not a good idea as it will go out of date ....
Do a query as above but the MSysObjects table contains some fields/records that you don't need so I would restrict the output slightly:

Code: 
SELECT Name, Type, Flags, Connect, Database
FROM MSysObjects
WHERE ((Not ((Name) Like "MSys*" Or (Name) Like "f_*" Or (Name) Like "~*")) AND (Not ((Type)=3 Or (Type)=-32757)) AND ((Flags)<>3))
ORDER BY Name, Type;

As already mentioned, I have an example app for this purpose that you can download:
www.isladogs.co.uk

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 www.isladogs.co.uk
 
Last edited:
Pat Hartman said:
And we're back to the Database Window:love::love::love: I have hated the Navigation Pain (sic) since its inception with A2007:)
Click to expand...
Yes I know as you keep saying that. 😏
Personally, I much prefer it to the old database window.

So let me give you the opportunity to explain why you hate it so much ...and why you think the old-style database window was better.
Feel free to have a rant if you wish 😁
 
That was a fairly restrained rant . . . 😎

Yes, I understand and agree with all of that but how often do you actually make those changes?
I have many very large databases, in some cases with thousands of objects and find it much easier to find objects using the nav pane.

In particular, I like being able to view different types of object simultaneously which wasn't possible with the old database window.
Going from one object type to another required multiple clicks and was done over & over again - lots of time wasted
I use the Search feature regularly. It a HUGE time saver.

95% of the time I'm using the same navigation pane view: All Access Objects, Sort Ascending by Name, View ...List
I rarely bother with Details view and like you gave up adding Descriptions to database objects years ago as I couldn't work out how to read them from e.g. a report
Similarly, I rarely need to change the Sort method but I welcome the many options available for those rare occasions.
Its even more unusual for me to change the Category and personally find Custom groups confusing.

Overall, the nav pane adds a lot of functionality, most of which I don't use ...but those features may well be the preferred way of working for others
 
You must log in or register to reply here.

Similar threads

B
VBA class modules & factory design
2
Replies
28
Views
868
MajP
MajP
P
Is there a way of deleting all code lines beginning with debug.print across all modules using VBA?
2
Replies
26
Views
1,634
DickyP
DickyP
isladogs
Hidden & little known features in VBA and the VBE
2
Replies
31
Views
2,447
DickyP
DickyP
V
Run-time Error 91 variable not set (class Modules)
Replies
9
Views
944
VSolano
V
N
MS Access VBA Vs JAVA
Replies
2
Views
667
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom