Microsoft Access - List Tables?

[rgusaas]

I have several MS Access (mdb) databases with 30+ tables each that I need to extract the data from into a single output.

From the Directory tool and Dynamic Input tool I am not seeing a way to get the list of tables so I can append the tables into a single output.

I have followed the examples of inputting multiple tabs from Excel workbooks but do not see any examples using MS Access as the source. 

Thanks in advance.

[SeanAdams · Accepted solution]

Hey @rgusaas,

One of the easy ways is to do a standard input control, and run a specific query against your access database which queries the master data and brings back the table names & details.

https://stackoverflow.com/questions/201282/how-can-i-get-table-names-from-an-ms-access-database

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name 

Once you have a list of the tables, you can put this through a dynamic input to bring into Alteryx.

[ansonwun]

Hi, my file is accdb and when I ran this it gave a "no read permission on MsysObjects" error. The database is not password protected. I wonder if accdb files require a different connection method to work for this case? Basically I'm trying to load tables with certain name patterns into the Dynamic Input tool (e.g. just tables starting with names "t_2018")

Thanks in advance!