Disappearing Queries

[carylilly]

I'm having problems with a Microsoft Access 2000 database. Queries keep
disappearing. Generally, the queries disappearing are 2nd level meaning, I'm
using a query to develop another query. However, this is not always the case.
Whenever I convert the database to 2003 the queries just disappear and I have
to rebuild them from scratch. There are 100 or so that disappear and this is
not a viable option. Neither is having to rebuild these objects (using DAO
objects) every time I roll out a new version of the front end. Anyone have
any clues to why this is occuring.

I've tried creating a blank database and moving all the objects to it to get
clean (object) system tables and this did not work either. As soon as I
convert the database, they are gone.

It kills me that I had to create a table with two fields (qname and SQL) and
that I have to enter the query name, then the SQL used by the query and then
use this table to rebuild the query objects (db.createquerydef(qname,SQL).
Every second level query I build has to be recreated on an open event in case
it got deleted when I rolled out a new version of the front end.

There has to be a solution to this problem, but I've researched this subject
to death and cannot find anything on it (for whatever reason). I know I
can't be the only person out there experiencing this so any assistance would
be greatly appreciated.

 

[Jerry Whittle]

1. What are the names of these queries? They don't start with Msys do they?

2. Have you checked that they aren't just hidden? Go to Tools, Options, View
tab and checkmark both Objects in Show. See if they show up then.

3. What happens when you run the following query. If they show up, there's
something wrong with the database window.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*")
AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

4. Do you have Compact on Close enabled or do a frequent Compact and Repair?
I once ran into a problem where all changes were thrown away after a Compact.
Final solution was wiping the hard drive and reinstalling OS and Office.

5. Similar to #4 above, what happens when you use another computer? If the
outcome is different, you have a good idea of where the problem is.