Extract all tables from Access Database

[danchips24]

Hi,

I am trying to input all tables from a Microsoft access database into Alteryx. I've tried using a union statement but that keeps throwing this error:

I have also tried to look up a solution on the community page and the only thing i found referenced a batch macro. the person who proposed the question seemed to have the same issue as me with the batch macro. He could not figure out how to get the list of table names. Another use then posted a Microsoft article on how to get the names of the tables out of the access file. I did not find this helpful because i have many access files that i need to do this for and do not want to go through one by one extracting the table names. it would be much more efficient to set up a solution for one file and then copy for each file. Can anyone post an example of how they are able to get all tables exported out of a Microsoft access database and pushed into a .yxdb?

[Emil_Kos]

Hi @danchips24,

I never used the access database with Alteryx but quite often I see that people have problems with drivers.

Please check this post maybe it will help you:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Microsoft-Access-accdb-Input/ta-p/3...

[danchips24]

Hi and thanks for the reply!

However, I am not having trouble connecting to the Microsoft Access Data Base. My issue is that im only able to input one table at a time.

[Emil_Kos]

Hi @danchips24,

I was under impression that might be the reason why it wasn't working.

After searching for an answer for you it looks like you need a batch macro to achieve this:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Import-multiple-tables-from-Access-dat...

Apologies but I will not be able to help you with this. Maybe someone else can take a look into it. 

[SeanAdams]

hey @danchips24 

There's a hidden system table in MS Access that contains system objects (like a table that contains all the DB objects).  this is very common in databases - these are metadata or system tables.

Depending on your version of Access it should be a table called MSysObjects - and you can query that to get all DB objects (like tables)

Here's a link to an article on MS.com for you.

https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-list-all-tables-in-database-using-an...