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
•   CROSSTAB
•   UNION / UNION ALL
•   APPEND
•   UPDATE - including combined APPEND/UPDATE queries - the UPSERT (AKA UPEND)query
•   DELETE
•   MAKE TABLE
•   Data Definition (DDL) - including CREATE TABLE, ALTER TABLE, DROP TABLE, ADD COLUMN and DROP COLUMN
•   Passthrough

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

NOTE:
If you find that any of your queries cannot successfully be read/viewed/executed, please send me an email giving full details.



Future Plans

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





Downloads

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