Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Query works in Access but not in Excel (XP SP2)

    Hello,

    I have a query created in Access XP under WinXP that works fine in Access. The query takes data from another query and a couple tables then filters it for me.

    However, when I try it in Excel XP using the menu: Data | Import External Data | New Database query then I go into the database and select the query, I receive the following: "The specified field 'Screen 5a - Category Consistency-Elim'.'Cat-Abbrev' could refer to more than one table listed in the FROM clause of your SQL statement." When I click on HELP, it says, "Driver error. This error was returned by the driver. For more information, check the documentation for the driver you are currently using." I have few clues on what's going other than the following.
    I have several tables that are labeled for different quarters of data. The differently named tables have the same field names in them so I might have [4Q01 Data].[Category] and [3Q01 Data].[Category], etc. So, my guess is that Excel is getting confused by the longer names that are very similar. I just don't understand why everything is fine when it runs in Access but not Excel.

    Any help will be appreciated. Thanks!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Query works in Access but not in Excel (XP SP2)

    Excel uses Microsoft Query, which isn't quite the same thing as Access, and has it's own set of foibles and constraints. This is strictly SWAG, but I suspect one of two things is happening. It is possible that you are exceeding the maximum length of the string that Query uses to reference a table name and field name. The other thing that could be going on is that Query doesn't know to try to use a fully qualified name. You could try constructing the same query in the MS Query design tool, but I would simply use the export to dump the data from the query out to Excel. Hope this gives you some ideas.
    Wendell

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query works in Access but not in Excel (XP SP2)

    Wendell,
    Thanks for your response.
    Your suggestion of exporting it to Excel makes sense. Question:. I can't figure out how to get my 10 queries to be automatically placed into one spreadsheet - something I thought that past version of Access would do. I made a macro to run the various queries but each query overwrites the Excel file that the previous query was written to. I guess I don't understand the functionality of Access very well.
    If you have any more ideas, they will be very much appreciated.

Posting Permissions

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