Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Fields not listed in MS Query (Excel2k-SR1)

    Hi,

    I am trying to use MS Query to get some data from an Access .mdb into Excel.

    When I go to "Data", "Get External Data", "New Database Query" and the Query Wizard pops up, I select the predefined DSN that I made to point to the .mdb and then I can see a list of tables and queries in my .mdb. The problem is that when I click on the "+" next to the query that contains the information that I want to dump into Excel, it does not show me the query fields and it won't allow me to click on the ">" to add that entire Query to the "Columns in your query" listbox.

    I have definitely done this before using queries (as opposed to tables) to retrieve the results of queries that contain calculated fields. In this particular case, it will allow me to get fields from any of the tables and return the data to Excel, but not from any of the queries in the .mdb. That is no good to me as the calculations in the query in Access is the crucial part of what I am trying to do.

    I have encountered the situation before when my calculated field in a query contained the nz() function (or a user-written function) and MS query would fail to resolve the function and return an error. This is not the case here as the calculations are simple algebraic expressions.

    When I click Cancel and then the wizard asks if I want to continue editing this query in MS Query and I select yes, I then try and select the query in the "Add Tables" dialog box that appears, I get an error : "Can't access table C:CMLIMS.qryFactoredAssays". When I click on the help option in the error dialog, help tells me "The specified table can't be found, is locked by another user or by another query you're running, or contains no valid fields." This does not seem to be the case as the query definitely exists with valid data and is not currently locked as the .mdb is not open.

    Any ideas please ?

    Regards,

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-SR1)

    Is there any chance of using the sql statement in the Access queries directly in msquery?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-SR1)

    Thanks for the idea, I will bear it in mind for the future. I have not tried that in this case because the query in question is based on another query which also exhibits the same problem that it's fields are not available.

    For some unknown reason the query fields are now available in the query wizard, and I have no idea why. The same test data is in the tables and I haven't changed the queries at all. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks,

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Query Fields not listed in MS Query (Excel2k-SR1)

    If the database is on a server, check that you have the correct permissions to that file. Also, if the Access security model is invoked your ID need to be joined to the security database.
    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-SR1)

    Thanks for the tip. I have thought of this before when I have had the same problem. In this case, the db has not yet been secured.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-S

    I'm not clear whether this now means you have gained access to your SQL. FTR, I have always taken the straight workaround of pasting in the SQL from Access. HTH
    Gre

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-S

    In this case, with simple algebraic expressions in the calculated fields in the query, the problem resolved itself. I don't know what was wrong or why it fixed itself - I am not aware of having done anything to get around the problem.

    As regards using either the built-in VBA nz() function or my own user-defined functions in a query in Access and then trying to use MS Query to get selected results from that query into Excel, it does not appear possible. I have tried pasting the SQL code from the Access query window directly into the MS Query window, but it still doesn't allow one to do that. The error indicated that the referenced table was unavailable. It appears that MS Query is unable to resolve any functions other than standard mathematical ones.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Fields not listed in MS Query (Excel2k-S

    MS Query uses ODBC to communicate with the database. It doesn't start Access. The ODBC driver for Access only understands a subset of Access SQL.

    It doesn't support:
    <UL><LI>Some Access functions, such as Nz (Others *are* supported, such as DLookup and StDev).
    <LI>User-defined functions.
    <LI>Parameter queries.
    <LI>References to forms and reports.[/list]

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-S

    Thanks - now you tell me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    So the Access ODBC driver is the actual culprit . . . .

  10. #10
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Fields not listed in MS Query (Excel2k-S

    I am having this same problem. I recently upgraded from 97 to 2k/2k2/xp and am now unable to see the fields to select when clicking the "+" next to the tables and querries. Any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •