Why does one specific query (itself, not the data returned,) keep going blank?

Phil Smith 1 Reputation point
2022-02-02T00:30:54.437+00:00

Access 2016. I have a macro that runs a bit of code, which creates a query, (which is not run or otherwise involved going forward,) then runs almost a dozen Make Table queries to support the Final Query, and exports the Final Query to Excel, then exits Access.

About 80% of the time that DB opens and that macro runs, (scheduled once a day via task scheduler,) the Final Query export fails with "Query needs a destination field.."

When I go to open that query in design mode, it is empty. No tables or fields, nothing. If I look in SQL, it is empty. There is no SQL, except SELECT:

I have a backup copy of that query, I copy the SQL to the original, and run it, manually, it runs fine. When run next from the task scheduler, repeat from "About 80% of the time."

Any ideas on this one?

Phil

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
825 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Karl Donaubauer 1,646 Reputation points MVP
    2022-02-02T14:12:40.083+00:00

    Hi,

    Without knowing any relevant code it's just...

    <guesswork>

    If your code does sth like
    db.QueryDefs("MyQuery").SQL = "SELECT blabla"

    then don't forget to do a

    db.QueryDefs.Refresh

    in order to have the current version of the query(def) for the export.

    </guesswork>

    Servus
    Karl
    Access News
    Access DevCon