The Inspire Cyber Monday promo has been extended to Dec. 1! This offer is discounted off the Early Bird price and includes 20% off for conference-only passes and 10% off training passes.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Microsoft Access - List Tables?

8 - Asteroid

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.

17 - Castor
17 - Castor

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.


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.




8 - Asteroid

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!