What are the Limitations for Connecting to an Access Query in Excel
- by thornomad
I have an Access 2007 database that has a number of tables, some are fairly large (100,000+ records); I have created a union query to pull some of the same types of data from multiple tables into one large query for pivot table manipulation and reporting. For example:
SELECT Language
FROM Table1
UNION ALL SELECT Language
FROM Table2
UNION ALL SELECT Language
FROM Table3;
This works. I found, quickly, however, that a union query will not show up when connecting to the datasource from Excel 2007. So, I created a second query to reference the union query. Like so:
SELECT *
FROM [The Above Union Query];
This query works and it, initially, was accessible from Excel. Time passed, I've added more data. Suddenly, when I connect to my Access database from Excel my query referencing the union has disappeared. MS Access shows no signs of an issue (data displays in Access) and my other non-union queries are showing up in Excel 2007 ... but not the one that references the union.
What could be going on? Why did it disappear?
I noticed if I switch some of the referenced tables in the union query to a smaller table (with less rows) all of sudden the query appears in Excel again. At least, I think that's what the difference is. I really can't put my finger on why some of the union queries won't show up and some will.
Am stumped and need some guidance. Thanks.
