locked
query won't show up in Excel list RRS feed

  • Question

  • In Excel 2010, when I hit Data/GetExternalData/FromAccess  and direct it to my database, there are a few queries missing from the list that will link to Excel.

    These are not parameter or actions queries, but they do rely on multiple tables.

    I know this isn't a lot to go on, but has anyone seen this anomaly before and resolved it???

     - Mike

    Wednesday, June 8, 2011 8:42 PM

All replies

  • When you get to the query wizard GUI where yo uchoose columns ther is an option button.  Make sure you check all items in the options GUI.
    jdweng
    Thursday, June 9, 2011 12:10 AM
  •  

    Joel,

    The query I need to link into Excel is already built.

    Anything I can do about it now?

    Thanks.

     - Mik

    Thursday, June 9, 2011 1:03 AM
  • You can always get the SQL statements from an excel query by using the Query editor.  You just have to click on any cell that has data that is returned from the query using the mouse.  then go to the query menu in excel and locate the Edit Query Option (usally grayed out if one of the cells is not activated).  the Query Editor has a SQL button where you can get the text statements from an existing query.  The connection properties
    jdweng
    Thursday, June 9, 2011 3:42 AM
  •  

    Believe we're a bit out of sorts here.

    I don't have the Access query in Excel.  *That's the problem.*

    When in Excel, am attempting to link to a query in Access that is there for sure, but it does not show up in Excel's list that would allow me to choose it [link to it].

    Need to make *all* queries in that specific database available to link into Excel.

     

    Thursday, June 9, 2011 4:36 AM
  • Hi Mikf,

    I can't reproduce the scenario on my side. What is the version of your Access application? I tested on my side to link to a Access 2010 file which contains the queries. As a result, I can get all the queries listed in the Select Table. Here is the screen shots:

     

    Can you make sure that the queries you want to show are in the Access database? Try to compact & repair your database file to see whether the queries show in the Select Table.

    Hope this helps.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 15, 2011 5:56 AM
  •  

    Bruce,

    You've hit on exactly the problem, but it remains.

    The queries missing from that list are *absolutely for sure* in my database. [I wouldn't be able to use it without them.]

    They just don't show up on that list. Which is extremely frustrating.

    They are certainly multi-table queries, and also contain crosstab queries. They don't have any "hidden" properties or anything like that.

    I've Compacted & Repaired numerous times.

    Both Excel and Access are 2010.

    Any ideas?

    Thanx!

     - Mik

    Wednesday, June 15, 2011 1:40 PM
  • Hi Mik,

    I have done some research about this problem but hard to find the workaround. Wait to see whether other experts can give you some suggestions.

    Try to create  a new database and export the tables and queries to another the new database to see whether it works well.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, June 16, 2011 1:06 PM
  • I have it narrowed down to one column.

    That column is an aggregate expression --- Net Income, which is comprised of RegSales and some other columns in the query. ([Reg Sales] + [Whatever] + [Other]).

    When I remove the Net Income column, the query shows up in Excel's list.

    Furthermore, if I remove RegSales from the Net Income column, the query will show up in Excel's list.  *** Even though the RegSales column itself shows up in every instance when imported into Excel.

    To summarize, the query will not show up in Excel's Select Table list while RegSales is part of the Net Income column.

    The RegSales column is comprised of columns from other queries.  Shouldn't be an issue. [????]

    Any ideas??????

    Thanks again.

     - Mik

    Thursday, June 16, 2011 5:43 PM
  •  

    Even stranger ....

    Instead of doing [Reg Sales] + [Whatever] + [Other] in the query, is used Sum([Reg Sales] + [Whatever] + [Other]).

    That did it, the query shows up in Excel's Select Table list.

    HOWEVER -- even though in the query the sum of these columns is correct, when linked to Excel *some* of the rows' Net Income is incorrect, by variances I can't ascertain.

    Ie Regsales 1000 + Whatever 500 + Other 500 = 2000 in the query, and when I sum the three columns in Excel.

    But when linked in Excel the sums are not correct; the above example's Net Income would be 1544.07, but other rows sum properly.

    No idea what's going on.

     - Mik

    Thursday, June 16, 2011 6:26 PM
  • The problem is probab;ly due to the number of bit in the Sales number.  The SQL query is including leading spaces, trailiong spaces, and truncated decimal places.  You could also be having a problem if you are comparing a number string with a real number and with the character dollar sign.

     

    You may need to use the format function to force the number to a string when you do any comparisons.


    jdweng
    • Proposed as answer by Bruce Song Tuesday, June 28, 2011 12:35 PM
    Thursday, June 16, 2011 8:29 PM
  •  

    Even stranger ....

    Instead of doing [Reg Sales] + [Whatever] + [Other] in the query, is used Sum([Reg Sales] + [Whatever] + [Other]).

    That did it, the query shows up in Excel's Select Table list.

    HOWEVER -- even though in the query the sum of these columns is correct, when linked to Excel *some* of the rows' Net Income is incorrect, by variances I can't ascertain.

    Ie Regsales 1000 + Whatever 500 + Other 500 = 2000 in the query, and when I sum the three columns in Excel.

    But when linked in Excel the sums are not correct; the above example's Net Income would be 1544.07, but other rows sum properly.

    No idea what's going on.

    Can you post this database somewhere I could download it, or else e-mail it to me (in compacted and zipped form) at the address you can get from my website?  If possible, post/send a cut-down version that includes only the elements necessary to demonstrate the problem.

    I'd like to look at it, time permitting.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Sunday, June 19, 2011 2:20 PM
  •  

    Thank you all for your assistance.

    Managed to get it all working by re-finessing much of the relevant table/query structure that caused the anomalies.

    Those anomalies remain in the previous database version, and are still inexplicable.

    However, will just move on now that it's working.

    Thanks again.

    • Proposed as answer by Bruce Song Tuesday, June 28, 2011 12:35 PM
    Sunday, June 19, 2011 2:57 PM
  • As a workaround, choose any item in the list to create the connection, then edit it afterward. On the Definition tab, in Command Text, change the table name to the name of your query. Works every time.

    I suspect it's caused by the ODC file that is created in the background the first time you connect to a database.
    Friday, July 1, 2011 9:51 PM
  •  

    Per my msg above, managed to get it resolved by redoing some of the query formulas.  Today I added some columns with IIF(table1 isNull, table2, table1) formulas and it doesn't show up in the Excel list anymore.

     

    Know exactly what you mean with your workaround, and made numerous attempts.

    Does not work.

    Thanks though.

    This anomaly is just plain frustrating.

     

    Saturday, July 2, 2011 2:44 PM
  • Access formulas don't work in a query from Excel.  This is a known issue for a long time.  the was the issue was worded it wasn't clear that the iss was only with queries with formulas in it.  I'm just make it very clear if peoiple in the future read this posting what the real solution to the problem is.  there isn't a solution except to remove the formulas to get queries to work from excel.
    jdweng
    Saturday, July 2, 2011 3:27 PM
  •  

    Respectfully, that is innaccurate.  At least by my experience.

    I have numerous Access queries containing formulas in their columns, that are linked into Excel sheets and work fine.

    Including the IIF formulas outlined above.

     

     

     

    Saturday, July 2, 2011 3:40 PM
  • Hi Dirk

    I am experiencing a similar problem (i.e. I can't get one of my access queries to appear in excel)

    If you are intersted I could e-mail you a tripped down copy of my database.

    Regards

    Chris

    Friday, July 13, 2012 6:52 AM
  • I had the same problem, but it seems to work if you do it this way:

    From the Data Tab select 'From Access' > New Source > ODBC DSN > Ms Access Database > browse for access Database file 

    You should end up at the 'Select Database and Table' and all visible queries and tables should be in the drop down box.

    Regards,

    Leandra

    Monday, May 27, 2013 6:48 AM
  • I had exactly the same problem as Mike and Leandra's New source > ODBC DSN workaround worked. Using 64bit 2013 and didn't have to change any of my Access queries.

    -Curtis

    Friday, July 12, 2013 1:13 AM
  • Been having the same issue, google brought me to this discussion.  Your tips have helped me get further but still not able to use the query in my Access 2010 database. The New Source --> ODBC DSN workaround lets me select my query, however once I select that query and pick a place to dump the data in Excel, it gives me an error saying:

                [Microsoft][ODBC Microsoft Access Driver] Undefined function 'nz' in expression.

    Of course, nz() is a very useful function in Access allowing null values to be handled with expected results.  Removing these expressions in not an option in my query.  Any one else solve (or at least encounter) this error (in spite of it being a successful workaround that lets you at least *see* the query in the list)? 

    I'm guessing this is similar to what the others were saying about certain functions not being recognized/acceptable to Excel and thus the query not compatible.  Really hoping I can get past that...

    Thanks,

    -Peter


    • Edited by Peter Moses Thursday, September 5, 2013 9:56 PM
    Thursday, September 5, 2013 9:55 PM
  • I think you can get it to work if you write your own macro using OLEDB in excel.  OLEDB you should be able to add parameters.  I know this works in VS when using Jet as the driver so it should also work in an excel macro using the Jet.

    jdweng

    Thursday, September 5, 2013 10:04 PM
  • I had the same problem, but it seems to work if you do it this way:

    From the Data Tab select 'From Access' > New Source > ODBC DSN > Ms Access Database > browse for access Database file 

    You should end up at the 'Select Database and Table' and all visible queries and tables should be in the drop down box.

    Regards,

    Leandra

    Thanks for this tip.  It worked for me on a recent issue.

    The key here, as others have noted, is that using ODBC to connect to a Access database probably means that Access-specific constructs (such as NZ) cannot be used in the queries.

    Currently, for me that is a not an issue but it's something to be aware of.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    Wednesday, October 30, 2013 2:31 PM
  • Following some ideas in this thread about the details of the access query that is not showing up, I found that if my query included the following:

    MonthName: MonthName(CLng(Mid([yearmonth],5)))

    to display the name of the month found within a "yyyymm" string, the query would not show in excel as a data source

    However, changing the details to the following:

    MonthNbr: Mid([yearmonth],5)

    it did show up.

    Thursday, February 6, 2014 8:05 PM
  • Hello, I know this thread is old, but it is still relevant.  I am currently having this happen to me where some queries within a single Access database are not showing up in the list of available external data sources in Excel.  Am I to understand that a query's selectability as an external data source in Excel depends on the way formulas have been coded within the query within MS Access?  

    I have experienced cases where if the query is missing from the data source selection list in Excel, I change and run the query as a "make table" query rather than a select query, and the new table will appear in the selection list (I think some have mentioned this workaround earlier).  But it doesn't work in all cases, as I have also found out with the query formula edits.

    This is now happening to me on an increasingly consistent basis with numerous unrelated Access files, and having a noticeable effect on productivity.

    Friday, October 24, 2014 1:23 PM
  • I had the same problem, but it seems to work if you do it this way:

    From the Data Tab select 'From Access' > New Source > ODBC DSN > Ms Access Database > browse for access Database file 

    You should end up at the 'Select Database and Table' and all visible queries and tables should be in the drop down box.

    Regards,

    Leandra


    You are my God d*** hero. This worked beautifully.
    Wednesday, May 27, 2015 6:06 PM
  • LG,

     Thanks -- problem has been bothering me for a while.  You are brilliant.

    pw

    Tuesday, June 30, 2015 5:15 PM
  • And while that solution worked perfectly, I found that queries with formulas including NZ as well as CONCAT related aren't importable through this process.
    Tuesday, September 22, 2015 12:38 PM
  • Hi,

    Just a note to say I encountered the same problem, and after much trial and error I found that the cause was the use of the 'HyperlinkPart' function in the Access query. As soon as I removed this the query started showing up in the list of tables when importing to Excel. So that's another one for 'the list' of Access functions that prevent Excel importing, if that's of any use to anyone...

    Rob

    Thursday, March 31, 2016 3:42 PM
  • There are a variety of reasons why the ODBC wizard cannot find certain queries. Most commonly this occurs when I have a complex, nested set of queries: I've always suspected that this is simply revenge on the part of the wizard because it has to work harder. It is an elegant revenge because this makes the root cause all that much harder for us to track down :)  

    The underlying issue is often the presence of a linked table somewhere in the nested query structure. Use of a UDF within the query can also foul things up. If the root cause isn't resolved, short of creating your own macro for populating the Excel table, the easiest and most reliable solution is to create a local table within Access based upon the problem query, then link Excel to that local table. I do this anyway sometimes to speed up the Excel table refresh.

    Once you've created the local table, you can write a macro in Excel to simplify refreshing it, something like:

        RunSQL "Delete * from [LocalTable]
        RunSQL "Insert into  [LocalTable] Select * from [ProblemQuery]"


    Tuesday, June 14, 2016 1:41 AM
  • In my case, I had changed the name and location of the access database to do some testing. after completing the testing (which included the modification of an access query and subsequent extract of the that data to excel), I save the data base in the original location and with the original name.

    Now when I try to use MS Query to extract the table, I see the table I am after, and the resulting data extract comes from some other location not the one in  the Data> Extract> Properties reference. I suspect it is saving the connection information in some system location and when it sees the reference it does not try to refresh the names of all the tables in the Access database. The net result is that I can't get to the data in the current Access DB!

    Thursday, August 11, 2016 10:45 PM
  • I know it 5 years later, but I had same problem with MS Access 2016. Turns out that queries is missing in Excel connection settings if they have sub-queries in WHERE section. I have rewritten queries to use JOIN instead of having sub-query in WHERE section and queries now are showing in Excel connection settings. IDK if it helps to somebody
    Sunday, December 4, 2016 1:05 PM
  • Same Problem Here.  

    Seems like another Microsoft snafu since they make BOTH programs.  You would expect them to work together.  

    Wednesday, February 15, 2017 7:22 AM
  • All, 

        I think the main culprit is the "Nz" function, I rewrote the queries with Nz function with IIF instead of Nz and all the queries show up and also work in excel now. I was wondering if DSUM/DCOUNT was the issue, but it was not. I would suggest all of you having this problem to check if you have any query which uses Nz function, then redo them with a simple iif check and it works ok, just little tedious to write.

    Thanks,

    Friday, March 17, 2017 7:53 PM
  • I, too, think the NZ function is causing the issue.  Excel can't interpret the NZ function, so it completely ignores any query that utilizes this function.  You can create a Make Table Query, off the Query that uses those NZ functions and when you write everything to a new table, reference that from Excel.  That will work.  I've done that in the past, as a workaround.


    MY BOOK

    Saturday, March 18, 2017 6:36 PM
  • <<From the Data Tab select 'From Access' > New Source > ODBC DSN > Ms Access Database > browse for access Database file >>

    You rock! Thanks so much.

    • Edited by TMuller Monday, May 22, 2017 6:26 PM
    Monday, May 22, 2017 6:25 PM
  • I, too, think the NZ function is causing the issue.  Excel can't interpret the NZ function, so it completely ignores any query that utilizes this function.  You can create a Make Table Query, off the Query that uses those NZ functions and when you write everything to a new table, reference that from Excel.  That will work.  I've done that in the past, as a workaround.


    MY BOOK


    Just change your

    Nz([FieldToCheck],AltValue)

    to

    Iif(IsNull([FieldToCheck]),AltValue,[FieldToCheck])

    It's not much longer, does exactly the same function and is Excel compatible.


    • Edited by Steve Cripps Monday, October 16, 2017 4:10 PM
    • Proposed as answer by wilkidj Wednesday, November 29, 2017 12:57 PM
    Monday, October 16, 2017 4:01 PM
  • Brilliant - Many thanks Steve.
    Wednesday, November 29, 2017 12:57 PM
  • I had the same problem , what i did was simple but efficent, i create a view from the query that didnt appeary and then seddunly it appercs on excel tab. hope this help u. Im no good at english sorry.
    Monday, March 12, 2018 4:56 PM
  • I have the same problem.  What I discovered is that a query (call it 1) that I could not see in the list had a VAL() function in it.  Another query (call it 2) I could see in the list was virtually identical but it did not have VAL() in it.  I removed the VAL() from 1 and then I could see it in the list.  So it seems that certain functions in an Access query will prevent excel from connecting to it. 
    Friday, August 17, 2018 12:00 PM