Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

MS Access 2007,2010,2013,2016 Metadata for 'Table or Query' list

I couldn't find anything where someone has submitted an idea yet and solution proposed by Paul N. (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/List-of-Tables-in-Ms-Access/td-p/30020...) does not work when attempting through Alteryx (that SQL works directly in MS Access).

 

I'd like that have the metadata option similar to xlsx input where you can output a list of all the tables in the file.  That data can then be used for a dynamic input to iterate through those different tables in a more automated solution.  As an example, if I have a list of accdb's that I want to parse through to scan the contained data for something of interest, I'd have to currently view each table one by one for each database which is highly time consuming.  Whereas if I had that table list from the metadata, I could loop through those searches must faster by using the full file path as the iteration. 

 

Thank you

5 Comments
Andrew_Meade
6 - Meteoroid

I wanted to share that I came across a viable solution that has some metadata options (although not all).  You can map the SP URL as a network drive and then use the Directory tool to read the metadata from that drive.  For my use case, I only care about documents that are saved in a SP so this allows me to pull down the specifics I need to see (file name, path, creation time, etc.).

SeanAdams
17 - Castor
17 - Castor

Hey @Andrew_Meade ,

Can you not just query the meta-data tables in MS Access to get the info you need?

https://www.opengatesw.net/ms-access-tutorials/Access-Articles/Microsoft-Access-System-Tables.htm

 

 

Andrew_Meade
6 - Meteoroid

Hi @SeanAdams 

I was looking for a solution that could all be done within Alteryx.  Essentially I was scanning network drives and parsing through different file types to identify personal information in those files (e.g., SSN, date of birth, etc.)  I didn't want to step outside of my workflow to do something special with MS Access databases.  My existing workflow looks at csv, txt, xlsx, sas7bdat, and pdf... I was hoping to add MS Access when I initially posted this.  Thanks

SeanAdams
17 - Castor
17 - Castor

Thanks @Andrew_Meade - I think you can still do this within Alteryx, but it may take a few steps:

a) you need to get the Admin role added be able to query a particular meta-data table called MSysObjects.    I think you may be able to do this as a one-time exercise in VBA.

🙂 this is the tough part, but it is a one-time effort

b) within Alteryx, you then open each MS Access Database, and query "select * from msysobjects"

MSysObjects is the table that stores the meta-data on tables, views etc.

 

Here's a link that describes how this meta-data table works

https://www.devhut.net/2010/06/12/ms-access-listing-of-database-objects/

 

 

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes