Version 2.12 First Published 1 Sept 2022 Approx 1.9 MB (zipped)
Recently, I saw this thread by an UtterAccess forum member: Extract Metadata of Queries in the Navigation Pane
I'd like to extract meta data of queries in Navigation Pane using VBA:
• Query name
• Query SQL statement
• Query properties (right click on anything object in the Navigation Pane and choose Properties).
This is where I can type in some text to explain the purpose of the object.
• Date created / edited
Can someone point me to the right place? Thanks!
This was easy to do by adapting the example app supplied with my article : How Access Stores Queries - Design vs SQL View
The original app included query name, query type, last saved view and last saved date. The main form looked like this:
I modified the original app in accordance with the forum post as follows:
• added DateCreated and Description fields using the query properties
• added a View SQL button
I then decided to extend this to allow users to view the query in SQL view, design view and datasheet view
The design view and datasheet view items just use standard Access functionality
Queries can be edited in each of the views. In SQL view, if the query SQL is invalid, an error message will be shown
You can choose CANCEL to restore the old SQL or click OK to view the error details first
Click the Restore Old SQL button to recover the original SQL
For action queries, selecting datasheet view runs the query and the table affected by the query output is displayed
The app has been extensively tested on a wide variety of queries including examples of each query type:
• SELECT - including INNER / OUTER / CARTESIAN / NON-EQUI / AMBIGUOUS JOINS as well as AGGREGATE queries
• UNION / UNION ALL
• UPDATE - including combined APPEND/UPDATE queries - the UPSERT (AKA UPEND)query
• MAKE TABLE
• Data Definition (DDL) - including CREATE TABLE, ALTER TABLE, DROP TABLE, ADD COLUMN and DROP COLUMN
The app is currently being supplied as a 32-bit or 64-bit ACCDE file and includes examples of each query type that I have tested with this utility.
You can also import your own tables and queries and test those as well
If you find that any of your queries cannot successfully be read/viewed/executed, please send me an email giving full details.
I also intend to convert this utility to an Access add-in in the near future.
That will allow you to use the add-in from any Access application and view the queries using the add-in functionality
I am also working on a query multi-viewer with similar functionality.
That will allow users to view all 3 query views (SQL/design/datasheet) at once on the same form
Click to download: (select the correct bitness for your version of Office)
Query Metadata Viewer (32-bit) 32-bit ACCDE file (zipped)
Query Metadata Viewer (64-bit) 64-bit ACCDE file (zipped)
A video demonstrating this example app will be made available on my Isladogs YouTube channel in the near future.
Colin Riddington Mendip Data Systems Last Updated 1 Sept 2022
Return to Example Databases Page
Return to Top