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

[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.

 

[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

 

[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

 

[strive4peace]

@SachAccess

> Need help to understand Linked Table Manager.

the Linked Table Manager tool helps you change the path(s) to your back-end(s)

> the purpose of Linked Table.

use linked tables when more than one person will be using the application. If that is the case, put tables in a different database(s) (back-end(s)). Then make a front-end database that links to the shared back-end(s). The front-end (FE) will have queries, forms, reports, macros, and modules, and possibly some resident tables ~ however, the tables to share will be linked to a back-end(s) that all can share. Each user will have their own copy of the front-end.

 

[SachAccess]

Thanks a lot for the help. Have a nice weekend.

Linked tables are just that. In the Front end where all the forms, reports, queries, macros, and modules are, the tables are not physically present but instead they "link" to a different database which is called the Back End. It is the Back End which can be shared to support multiple users.

In a proper development environment, you have a minimum of two versions of the BE and usually three. Production (the final version used for real work and NEVER for testing), System Testing (the version that is used by the users for testing new versions of the FE), Unit Testing (the version used by the programmer to implement changes and test them).

The Linked tables manager is used to link between the various BE versions and is almost always only used by the programmer. It is very rarely used by the users since very few applications would ever have a need to switch between versions of Production data.

 

[SachAccess]

@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

Thanks for the help. Have a nice weekend.

 

[SachAccess]

Thanks for the help. Please correct me if am wrong. I will use the MS Access file I have as an example.
So, the file with Linked Tables are Front End. This file has all the forms, reports, queries, modules and macros.
The file which has original table will be called as Back End.
I checked that file too, it only has tables, there is nothing in the file apart from the tables.

So the Linked Tables in the Front End are just mirror copy of the Back End tables.
The changes done in the Front End Linked Tables affect Back End tables too ? And vice versa.
I am not sure about my my understanding.
Just trying to make sure if my understanding is correct.

PS - Few tables are linked to SharePoint. What pre-caution I should take while accessing, editing tables linked to SharePoint.
What are the things that I should keep in mind while working on tables linked to SharePoint.
Thanks.

 

[isladogs]

The linked tables in the frontend are NOT mirror copies of the backend tables. They ARE the backend tables.
So any changes made in the front end are actually changing the backend tables.

It doesn't matter whether those linked tables are in Access, SharePoint, SQL Server etc. Exactly the same points apply.

 

[isladogs]

@Pat Hartman
Whilst your answer describes good practice, it isn't entirely correct. For example:
a) you can change the structure of linked Access BE tables from the FE using a DDL query and SQL Server BE tables using a SQL script run from the FE
b) linked Excel tables can be updated in Access without using automation, To do so, you just need to set the IMEX value = 0

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

This method allows you to append new records & update existing records.
However you cannot delete records using this approach

 

[SachAccess]

The linked tables in the frontend are NOT mirror copies of the backend tables. They ARE the backend tables.
So any changes made in the front end are actually changing the backend tables.

It doesn't matter whether those linked tables are in Access, SharePoint, SQL Server etc. Exactly the same points apply.

Thanks for taking time on weekend and helping me.

 

[SachAccess]

Regardless of whether the linked tables are Jet/ACE, SharePoint, SQL Server, or any other RDBMS, When you make a data change via Access using a form, query, or directly to the table in DS view, the change is made directly to the physical table in the BE Database. There is no intermediate storage. If you run an Action query such as update or append or delete, you might get a "do you really want to do this" message depending on how you run the query but that's it.

If you need to change the structure of a linked table, you CANNOT do it in the FE. You MUST open the BE database and make the change there. However, you need to ensure that no user is actively working with the BE at the time you change the physical structure so this must always be done in a planned, carefully monitored way. Best to make BE changes in the evening or on weekends when it is easy to lock users out.

Also, not all linked tables are updateable. For example, tables linked to flat files such as .txt, .csv, or .xlsx CANNOT be updated through the Access interface. Excel files (not .csv files) can be updated using OLE automation. to change other flat files, they need to be rewritten. You need to read the file sequentially using one filespec and then write out all the records including the updates using a second filespec. Although, you can append data to the end of a flat file, I've never attempted to do this via Access. In 25 + years, I've never had a reason to even try although in my mainframe days with COBOL, that process was very common.

RDBMS files such as SQL Server and SharePoint must have primary keys or unique indexes to be updateable. You also must have permission from the database manager. There are other rules also.

Thanks a lot for the help! Now slowly I am understanding it. When I was able to make changes in the tables in both back end and front end then initially I thought, what is the purpose how having tables at two different places.

'If you need to change the structure of a linked table, you CANNOT do it in the FE' this line helped clearing my doubt.

 

[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.

HI,
I am a little late to the party as always, however here is a great resource for all your VBA programming needs, While it is a bit dated, it is still very relevant today...


Access Cookbook, 2nd Edition
by Ken Getz, Paul Litwin, Andy Baron
Released March 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 978059600678

You can purchase the book from O'Reilly directly or on Amazon. Digital Download and Companion Code is available.

 

[SachAccess]

HI,
I am a little late to the party as always, however here is a great resource for all your VBA programming needs, While it is a bit dated, it is still very relevant today...


Access Cookbook, 2nd Edition
by Ken Getz, Paul Litwin, Andy Baron
Released March 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 978059600678

You can purchase the book from O'Reilly directly or on Amazon. Digital Download and Companion Code is available.

Thanks for the help.