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)

spaLOGICng

Member
Local time
Today, 04:41
Joined
Jul 27, 2012
Messages
98
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.
SELECT * FROM mSysObjects
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
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:
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,724
I have an example app for this purpose that you can download:
And we're back to the Database Window:love::love::love: I have hated the Navigation Pain (sic) since its inception with A2007:)
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
And we're back to the Database Window:love::love::love: I have hated the Navigation Pain (sic) since its inception with A2007:)
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 😁
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,724
It's all about clicks and the efficiency of long lists. Anything works when there are 20 items in the list but once you get to the hundreds in a real application, some display methods are just more efficient. And the db window was significantly more efficient. The annoying part was getting it out of the way. That they could have fixed.

1. If I want to close up a section instead of scrolling through it repeatedly, I have to scroll all the way to the top to get to the shrink button. The tabs on the database window solve this problem. MS could add 6 (5 for objects, a 6th for all) tabs across the top under the search and probably save me 10 minutes a day. It's not much but it is the annoyance factor. Don't let your interface annoy people, especially when it is easy to fix.
2. If I want to show the description or the date info, I have to right click on the header, scroll to the view by, and click on Details. This now increases the lines for each item to 3 and reduces the number I can see on the screen by more than 50%. That causes lots of extra scrolling so I never use this as my default view. So I have to "undo" the clicks to get back to my default view.
3. The real pain is if I want to sort the lists. First, I have to click on the header, scroll to the sort by, and choose an option (up or down). Then I have to go back and click on the header, scroll to the sort by, and choose a category. And finally, if I want to sort on a date, I have to go through #2 again. That is SIX clicks. In the DB window, you just click the header to sort. Click a second time to reverse the order. I've stopped documenting my objects because it is just too painful using the Nav Pain(sic) plus, it adds a fourth line to the item list further reducing what I can see. And then I have to "undo" the clicks to get back to my default view. This is the one that is most aggravating.

The thing I do like is the search option but that's about it. Sometimes, when people complain about a feature, such as the security or the db window, MS rather than listening to the remarks and actually understanding the problem, they just throw the baby out with the bathwater. They could have just added a search to the db window and maybe a way to minimize the window and dock it. Complaints stop:)
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,724
As I said, I like the search but they could have added the search and the 6th view to the db window and not changed any other thing and been well ahead of the game.

I also don't change the view very often but only because it is way too annoying, not because I don't want to.

I'm fine with change. My entire life has been about change. What I don't like is change for the sake of change and we get way too much of that from MS. In the process, they break things and don't add value. All in all, the Nav Pain (sic) did not add value. The ribbon at least is neutral (not better, not worse) but it "broke" apps that used custom menus because there was (and still is not) a built in way to create custom ribbons. It also takes up too much vertical space. The old menus were far more efficient space wise. I do like the ability to customize the ribbon to some extent.
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
I don't change the view very often as it is rare that my preferred view isn't suitable.
I do expand/shrink the object type groups regularly but that's one click
But the main benefit for me is being able to see objects from all groups at once ...as well as searching which I couldn't do without.

Anyway, thanks for sharing your reasons. We'll just have to disagree about the nav pane.

Also can't remember whether you could completely hide the old database window as you can with the nav pane.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,724
I do expand/shrink the object type groups regularly but that's one click
Only after you've scrolled to the top of the group.

Yes, you could completely hide the original db window. It also stayed in the background when forms opened if you were working in design view so it didn't obstruct the view of a form.
 

strive4peace

AWF VIP
Local time
Today, 06:41
Joined
Apr 3, 2020
Messages
1,001
hi @SachAccess

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

adding onto Pat's SQL on the MSysObjects table ... this uses Switch to sort and elaborate. Make a new query, switch to SQL view, and paste this in!

SELECT Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99) AS Ordr, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked Table",[Type]=8,"Relationship",[Type]=3,"container") AS ObjectType, m.Name, m.Type, m.Flags
FROM MSysObjects AS m
WHERE (((m.Type)<>3) AND ((m.Flags)>=0) AND ((Left([Name],1))<>"~" And (Left([Name],1))<>"{" And (Left([Name],1))<>"_") AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"MSys"))
ORDER BY Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99), Nz(Switch([Type]=1,10,[Type]=5,20,[Type]=-32768,30,[Type]=-32764,40,[Type]=-32766,50,[Type]=-32761,60,[Type]=4,70,[Type]=6,80,[Type]=8,90,[Type]=3,100),999), m.Name;
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
@Pat Hartman
I've just created a simple 'Nav Pane Helper' which provides several of the navigation pane options in one click

NavPaneHelper.PNG


See a short video below


The Sort options haven't yet been done
If it looks useful, I'm happy to add additional functionality

Any comments appreciated before I spend any more time on this
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
LOL!
Would you believe that I can't find the commands to sort the nav pane ascending / descending
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,129
YouTube created the 'short video' format automatically as it was less than 30 seconds(?)
Intended for watching on a smart phone

I've added extra functionality (category & nav pane options)
Will upload when I've found the command(s) to sort the nav pane - it should be easy but ....
 

strive4peace

AWF VIP
Local time
Today, 06:41
Joined
Apr 3, 2020
Messages
1,001
if anyone can figure it out, it is you ;) ~ good luck @isladogs Colin . Hey an idea for your interface ~ textbox to enter pattern with wildcards like q*vid*stat*
 

SachAccess

Active member
Local time
Today, 18:11
Joined
Nov 22, 2021
Messages
389
Hi,
I tried two different versions to get the results, it was by accident.
VBA code gave me details of 41 queries whereas SQL query gave me details of 77 queries.
I am not able to understand reason behind the difference.
Both the methods were tried on the same MS Access DB.
I have excluded queries starting from '~' from the count of 77.
Checked few random queries from the 77 result set, those are present in the MS Access DB.
Can anyone please help me understand this. Thanks.

VBA Code
Code:
'https://www.access-programmers.co.uk/forums/threads/is-there-any-vba-code-for-ms-access-which-will-return-name-of-all-the-queries-reports-form-and-modules-from-the-db.324186/page-2
Option Compare Database
Option Explicit
Sub mreport()
    Dim tbl As DAO.TableDef
    Dim que As QueryDef
    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, "---------------"
        Debug.Print "Container"
        For Each doc In cont.Documents
        If doc.Name Like "msys*" Then
            Else
                'Debug.Print doc.LastUpdated, doc.Name
                Debug.Print doc.Name
        End If
        Next doc
    Next cont
    ''''''''''''''''''
   
    'Table details
    Debug.Print "Table Details"
    For Each tbl In dbs.TableDefs
        If tbl.Name Like "msys*" Then
            Else
                'Debug.Print "~~"; tbl.Name, tbl.Connect & ""
                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.Name
                Debug.Print que.sql
        End If
    Next que
End Sub

SQL Query
Code:
Query to get all objects
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;
 

SachAccess

Active member
Local time
Today, 18:11
Joined
Nov 22, 2021
Messages
389
Need help to understand Linked Table Manager.

Not sure if it can done with new post, hence posting my doubt in existing thread itself.
I am getting object details with the SQL query.
Please note, I have changed all the actual names with dummy names.
I have this ‘tbl_Dummy’, path is ‘;DATABASE=MyDummyPath\MyDummyFile.accdb’
Type description is Table - Linked Tables or Files.

I have access to MyDummyFile. I opened this file, tbl_Dummy was present in this file too.
I am trying to understand what actual is Linked Table is, why it is used, the purpose of Linked Table.
I can see both the original table and linked table have same number of records.
Any links for reading will also be helpful.
 

strive4peace

AWF VIP
Local time
Today, 06:41
Joined
Apr 3, 2020
Messages
1,001
@SachAccess, here's another SQL statement to get info from MSysObjects for object names and types that uses SWITCH to specify the object type and ordering ... it also filters out temporary stuff

Code:
SELECT Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99) AS Ordr, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked  Table",[Type]=8,"Relationship",[Type]=3,"container") AS ObjectType, m.Name, m.Type, m.Flags
FROM MSysObjects AS m
WHERE (((m.Type)<>3) AND ((m.Flags)>=0) AND ((Left([Name],1))<>"~" And (Left([Name],1))<>"{" And (Left([Name],1))<>"_") AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"MSys"))
ORDER BY Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99), Nz(Switch([Type]=1,10,[Type]=5,20,[Type]=-32768,30,[Type]=-32764,40,[Type]=-32766,50,[Type]=-32761,60,[Type]=4,70,[Type]=6,80,[Type]=8,90,[Type]=3,100),999), m.Name;

you can add more columns as desired
 
Last edited:

Users who are viewing this thread

Top Bottom