Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    107
    Provided Answers: 1

    Unanswered: The query did not run, or the database table could not be opened

    Hi all,

    Using Access 2016.

    I connected to a query in a database to create some Pivot Tables - great.
    I changed the query slightly and it runs as expected.

    However, when I open the Excel Workbook and attempt to refresh Pivots, I receive the message,
    The query did not run, or the database table could not be opened.
    Check the database server or contact you database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.
    I started a clean Workbook and attempted same, but at Select Table Dialog, the query is not available.
    However, if I open the database and run the query, it runs very quickly and returns the expected results.

    I tried Compact/Repair - all results the same.

    Any thoughts?

    thx
    w

  2. #2
    Join Date
    Feb 2012
    Posts
    107
    Provided Answers: 1
    Bump.

    thx
    w

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suspect its more an excel issue than an access issue

    is it possible the db could be in use with an exclusive / read only lock on it when you try to open it from Excel

    you have made changes to the query since it first worked in Excel, what are those changes
    is it possible that you have changed the column mappings? added or deleted columns?

    what happens if you try to recreate the data consumer in a new worksheet in the current excel file.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2012
    Posts
    107
    Provided Answers: 1
    Thanks healdem,

    I try a new connection from the current workbook. The database is displayed with several tables and queries to choose from except the query I want is not shown as available.
    I tried the same steps from a brand new workbook - same result, the query I want is not displayed.

    I go into Access, run the query and export the results to Excel for Pivot Analysis.
    I should be able to do all of this using Data..Get External Data tools from within Excel and save to an .odc file - done it a million times.

    For some reason, not working for this particular query.

    Thanks
    w

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you tried inserting the data into another worksheet/workbook in the current file.... iE the aim is to try and prove if the fault is in Excel, or is in the current file.
    have you tried repair the excel file?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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