Home > Access, Tableau > MS Access query isn’t in the Tableau table list

MS Access query isn’t in the Tableau table list

Using MS Access in conjunction with Tableau isn’t always a painless experience, especially when it comes to the Access query. You might notice your Access query doesn’t appear in Tableau for you to choose. Alternatively you might find an Access query that returns records in Access returns zero records in Tableau.

All of this happens without any explanation, it’s a frustrating head-scratching moment. This article might help solve your problem.

 

My working Access query doesn’t return any records in Tableau

From my testing if there was a LIKE statement in the WHERE clause this caused Tableau to return no records. The query did appear in the Tableau table list but returned zero records. Options are:

  1. Remove the access LIKE condition and keep the filtering within Tableau
  2. Rreplace LIKE with standard text functions if possible, such as LEFT, RIGHT, etc.

 

My working Access query doesn’t appear in the table list within Tableau

If you have a UNION query in your Access database you will probably notice that you’re unable to see that query or and other query that refers to the UNION query within the Tableau table list. It isn’t possible to connect directly to an Access UNION query using Tableau.

The workaround is to save your Access union query and create a new standard query to select * from the union query.

For example save the following query as MyUnionQuery

 

SELECT [FieldName] FROM Table1

UNION

SELECT [FieldName] FROM Table2;

 

MyUnionQuery won’t appear in Tableau as it’s a UNION. However if the following is created it will appear in Tableau:

 

SELECT * FROM MyUnionQuery;

 

That didn’t work? Renaming your fields within the UNION query can also cause a problem, depending on what it is renamed to. There must be some reserved words, probably any that are standard SQL words such as Count and Sum, that are allowed as field names within Access. For example the following would not cause an error in Access but the query would not appear in the Tableau table list:

 

SELECT [FieldName] AS [Count] FROM Table1

UNION

SELECT [OtherField] FROM Table2;

 

Interestingly the following simple query does work in Access and also appears in Tableau:

SELECT [FieldName] AS [Count] FROM Table1;

 

None of my Access queries appear in the table list in Tableau, even the simple queries

I sometimes find only the table names are visible when I’m connecting to Access thru Tableau while Access is also open. Closing all of the windows within Access solves this problem, then reconnecting in Tableau.

 

You might find even more crazy things happening when using Access with Tableau, if so please to add to the comments.

Categories: Access, Tableau Tags:
  1. No comments yet.
  1. No trackbacks yet.