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

[spaLOGICng]

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]

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:

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:

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

 

[Pat Hartman]

I have an example app for this purpose that you can download:

And we're back to the Database Window I have hated the Navigation Pain (sic) since its inception with A2007

 

[Pat Hartman]

@spaLOGICng I guess you didn't see my query back in #14.

 

[isladogs]

And we're back to the Database Window 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]

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

 

[isladogs]

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]

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]

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]

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]

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]

@Pat Hartman
I've just created a simple 'Nav Pane Helper' which provides several of the navigation pane options in one click

See a short video below

- YouTube

Enjoy the videos and music you love, upload original content, and share it all with friends, family, and the world on YouTube.

youtube.com

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

 

[Pat Hartman]

That's great. Maybe you can sell it to Microsoft

 

[isladogs]

LOL!
Would you believe that I can't find the commands to sort the nav pane ascending / descending

 

[strive4peace]

very interesting approach, Colin @isladogs. Cool design and functionality!

I didn't know about "Shorts" ~

 

[isladogs]

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]

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]

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

'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

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]

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]

@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

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