SQL Deleted From Access 2003 Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've written an Access 2003 program that reads the SQL from a query, and
occasionaly deletes the SQL associated with that query. After reviewing the
code, I found that I had failed to release the QueryDef object I had set to
the query. Is this likely to be the reason for the SQL Deletion from the
query?

The query in question is Overly complex, referencing multiple crosstab
queries. I'm not particularly proud of it. If the unreleased Object is
unlikely to be the reason for the Queries underlying SQL statement being
deleted, then my next best guess is that my subconscious is psychically
deleting the SQL in shame of having written such poor SQL, as this doesn't
seem to happen with any other query.

It is well known Axiom that with great power comes great responsibility.
Should I discover I had such psychic powers, I would feel obligated to fight
the proliferation of Poorly written SQL where ever it could be found. Which
would cut into my TV time. That said, I would prefer to identify a different
cause to my dilemma.

Any suggestions?
 
While it is a good idea to set your objects to Nothing before you exit a
routine, I would be *very* surprised if that is causing this issue for you.

It is much more likely to be a problem caused by Name AutoCorrect. If a
field or table is renamed, this "feature" attempts to track the new names of
the objects and keep things working. In reality, it gets woefully confused
between the various objects and regularly corrupts your database. You can
imagine if it is trying to replace the SQL string with what it things it the
changed name of a some object, that this could result in the destruction of
the SQL string.

Try unchecking the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database.

For more info, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html
 
Many many thanks, I have not, as of yet, confirmed that Name AutoCorrect is
the cause, but it seem like the likely culprit. I do believe I may return to
fiddling with my remote. 8)
 
Back
Top