Disappearing SQL Code

[bgetson]

I have a pretty simple select query that does some minor calculations,
but for some reason, the SQL code inisde of the query keeps getting
erased. Every night I run a process from Excel that tells Access to
export the data from two different queries. The same subroutine
handles both queries (from a variable parameter) and uses the
DoCmd.OutputTo command to export both queries as an .xls file.

If the SQL is working beforehand, the process runs fine, and the
queries are exported. Afterwards, the query is only left with
"SELECT;". As far as I can tell, this is the only process that
references this specific query.

As a side note, the other query is a union query. Also, this process
has run fine in the past, and it is only recently when I added an
additional table to the database (which doesn't affect any queries)
that any of this started happening. The only change to the relevant
data structure is that I changed that name of a table I was
referencing. However, even if I create a fresh select query and copy
the SQL into it, the same problems occur.

Any ideas? It's getting tedious to copy the SQL in every morning.
Thanks for your help.

 

[Steve Sanford]

No way to tell from your post. Would you post the VBA code you are using?

How do you start the process?

 

[bgetson]

Alright, here is the code inside of Access:

Sub ExportQuery(qry As String, file As String)
DoCmd.OutputTo acOutputQuery, qry, acFormatXLS, "Y:\Spotlight Reporting
\" & file & ".xls"
End Sub

And here's what's run inside of Excel:

' Export Spotlight to Access
Dim Ac As Object
Set Ac = CreateObject("Access.Application")

Ac.Visible = False
Ac.OpenCurrentDatabase ("Y:\Spotlight Reporting\Spotlight
Ranking.mdb")
Ac.Run "DataTransfer", "tblData300", wkbk.FullName, i ' i = number
of systems

' Export Access Queries
Ac.Run "ExportQuery", "qryMaster300", "ExportedSpotlightMaster300"
Ac.Run "ExportQuery", "qryMarket300", "ExportedSpotlightMarket300"
Set Ac = Nothing

The first Access process updates the table that my queries reference.
The next two are successful, but the SQL inside qryMaster300 is
removed.

 

[Jerry Whittle]

Set Ac = Nothing

I wouldn't think that would mess up the query but might be worth checking by
commenting out that line for testing.

However, I think the root problem could be corruption. Try running a compact
and repair.

If that doesn't help, create a new database file and import everything from
your existing database into the new one. Then rename both .mdb files so that
the new one has the expected name.

For a really way out there, probably dumb, idea, go to Tools, Options,
General Tab and turn off Name Autocorrect on all of your databases. IMHO it's
a piece of do-do and the cause of many problems.