Query doest not show in Data Connections in Excel 2007

[adt29]

I'm not sure if I'm using the best key words but hopefully the below explains it better:



My Access database queries originally had values for fiscal year and month-- "2010" and month "Feb". I created a user form with a drop down menu that one can select the fiscal year and month from, and now the same queries that used to show up in Excel through the Data Ribbon-->Connections-->Add process no longer show. Only the tables do. I have at least 10 queries like this and would really like Excel to show them! What is the solution to this?

Because I have now created a macro that runs through all the queries taking the input from the user form, it would be extremely counterproductive to reverse it back to putting in the fiscal year and month manually. The only thing I can think of is to copy and paste the data in my Excel sheets from the Access queries but this is in no way efficient either. Any suggestions welcome!

[wannakatana]

I wish there were a reply as I'm getting the same issue, some queries show up in the "select table" dialog and some don't.

[sturmster]

I know this post is a little old, but ran into this same issues and this is what I found. It appears that when using the Excel interface to Access that the SQL has to very standard. ie. Access has an NZ function but that isn't standard SQL. If your query has a NZ function in it, the query will not show in your select list.

So, in my case, I changed the query to not use the NZ function. Instead I used an IIF statement testing for IS NULL. The query then showed up for me. I'm sure there are other non-standard SQL issues as well.

If you're really stuck, eliminate a column at a time from your query until you find the problem.

[BettyJordan]

I also had the same problem with formatcurrent function in a formula field in my query, I took it out and the query showed up. Strange!

[rsACC99]

I noticed Excel would not display any crosstab queries, and several others, for connection. My solution: Instead of selecting DATA | FROM ACCESS select DATA | FROM OTHER SOURCES | FROM MICROSOFT QUERY. This opens a familiar-from-2003 dialog which will lead you to the old Query Wizard. This wizard shows all the tables and queries.