Hello Matt Lukoff,
Were you able to resolve the issue reported in this thread?
If not, is it possible to share a small sample database with a example query? In addition, please test connecting to the source data from another microsoft tool such as microsoft Excel. This will test the Microsoft JET engine connectivity to determine if the MS engine is the source of the issue or if Tableau is where the issue is being encountered.
One additional note, the MS Jet engine does not support the "*" character in queries but instead uses "%". This is important if your query has a 'Like "*" operator as it will need to be modified to use the percent symbol.
I hope that helps. Please report back if additional questions or issues arise.
2 of 2 people found this helpful
MS JET (the driver used for MS Access) doesn't directly connect to UNION queries. The workaround is to wrap the union query in a regular SELECT query and then you can connect to it in Tableau.
See http://drawingwithnumbers.artisart.org/i-have-wee-data-microsoft-access-and-tableau/ for a whole lot more on Access and Tableau.
The "query for Tableau" in the attached has the follwoing SQL:
SELECT [Union Query].*
FROM [Union Query];
And here it is available in the data connection window:
Database1.accdb.zip 125.2 KB
BTW, technically it's not a table, it's just a query that uses the UNION
query as a data source. I think of tables as static - the data goes in and
only gets updated manually or through a query, whereas SELECT queries can
give very dynamic representations of the data.
On Mon, Jun 15, 2015 at 5:00 PM, Matt Lukoff <
Hi Jonathan, Matt, and Patrick,
Running into this same issue, creating the SELECT query suggested seems to work well on the Access side, but when I go to find it in the Tableau Data Source pane it's not available.
I have already ensured drivers are installed/up to date. Working with Access 2016 32 Bit and Tableau 10.0.1 64 Bit on Windows Server 2012 R2.
Unfortunately I can't share the data at the moment.
Any ideas as to why the new SELECT query would be visible and operational in Access but not Tableau?
Hi Stephen, it works as I now use this often. Did you add the select query as a new data source in Tableau? You have to do that too.
If your Access queries use any user-defined functions, VB code, and/or or Access parameters then they won’t pass through the MS JET driver (this is standard MS JET driver behavior and discussed in http://drawingwithnumbers.artisart.org/i-have-wee-data-microsoft-access-and-tableau/ <http://drawingwithnumbers.artisart.org/i-have-wee-data-microsoft-access-and-tableau/> ). Also if you’ve changed any field names or anything that would cause MS Access to throw up a warning or error message when the query runs (even if the query is just fine) that will cause the query to not show up in the list.
How you can test is to individually check whether each of the SELECT queries that are inside the UNION query are also available in Tableau. If one of those isn’t available then the SELECT query that is on the UNION won’t be available either.
I was having the same problem too, but i figured out it was just a syntax error.
1. Copy the union query from MS Access.
2. Connect your tableau to ms access
3. now paste that query into tableau custom query without the semi colon (;)
boom it works.
UNION ALL SELECT *