Using MS Access in conjunction with Tableau isn’t always a painless experience, especially when it comes to the Access query.
Although both Access and Tableau use MS Jet SQL, when connecting Access to Tableau it is not unusual for things to not work as one might expect. Often this is compounded by little in the way of explanation.
Common Access Query and Tableau difficulties
You might find an Access query that returns records in Access does not return any records in Tableau.
Or, worse, your Access query doesn’t appear in Tableau at all!
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 is a LIKE statement in the WHERE clause this can cause Tableau to return no records.
The query does appear in the Tableau table list but returned zero records. Options are:
- Remove the access LIKE condition and keep the filtering within Tableau
- Replace LIKE with standard text functions if possible, such as LEFT, RIGHT, etc.
My Access query doesn’t appear in the table list within Tableau
It isn’t possible to connect directly to an Access UNION query using Tableau.
If you have a UNION query in your Access database you will probably notice that you’re unable to see that query in the Tableau table list.
The workaround is to, first, save your Access union query.
Next 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, create the following and it will appear in Tableau:
SELECT * FROM MyUnionQuery;
That didn’t work?
Renaming fields within the union can also cause problems
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 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.