Query exists in MSysQueries, but not in db anymore

[chris]

As soon as program opens in Access 2002, error 3078 appears sayng that a
query is not found.

Query does not exist anymore in db, I deleted it some time ago, but exists
in MSysQueries.

I compacted db, decompile it also but those records from MSysQueries are not
deleted...

What can I do ?

Thanks a lot in advance!

 

[Allen Browne]

Create a new (blank) database, and import everything.

 

[chris]

Thanks Allen, Program runs on 2000 machines, only this one gave this error...
so is not easy following this solution :-(

 

[Allen Browne]

I don't understand that reply, Chris.

If it is a multi-user program, you *really* need to split it. If that's a
new concept, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

Once you have it split, you can replace the front end with an updated one
(without the corrupt query), without much difficulty.

If you need to automate that process across 2000 machines, this utility
might help:
http://www.granite.ab.ca/access/autofe.htm

 

[chris]

Sorry, let me rephrase it..

I used to have everything, forms & queries in 1 MDB. Then I used that MDB
and splitted forms/queries & tables in 2 MDBs (delete unecessary objects for
each MDB). Query that creates problem does not exist anymore in MDB with
forms/queries.

What is weird for me is that program runs on 2,000 machines without problem.
Only this machine gives thi error...

 

[Allen Browne]

Okay, so you, have a split database, with 2000 front ends. Each user opens a
*different* (independent) copy of the front end. One machine has a corrupt
front end; the others don't.

In that case, you could copy the front end MDB from any of the other 1999
machines, and overwrite the faulty front end on that one machine?

If the problem still occurs on that machine, you could go to any of the
other machines, examine MSysObjects, and see what's really going on. You are
trying to track down a disparity between CurrentData.AllQueries and objects
of type 5 in MSysObjects.

Hopefully you already have Name AutoCorrect turned off, so this is not just
bad tracking on Jet's part:
http://allenbrowne.com/bug-03.html

If it's still failing on this one machine after that, you are now trying to
track down what's different about that machine. Different Office service
pack? Different version of JET (this one's important if the minor version is
less than 8; irrelevant if it's .8xxxx or .9.xxxx). Service packs:
support.microsoft/com/sp

 

[chris]

Yes, each user opens a *different* (independent) copy of the front end.

I asked user to copy MDE from another machine that works, but problem is same.
But since file from another machine has the same problem, the MySysObjects
is the same, right ?

I checked the MDE from installation files I have and found that query that
creates problem is of type 5 in MySysObjects and it's Name field starts with
"~sq_cAdvanced_......". Which means that these are temporary queries created
by Access and not removed during compact, right ?

Name AutoCorrect is turned off.

Service Packs are the same, so I need to check the JET version now.

I am thinking of deleting manually these records.... but don't know whether
I will create any problem...

 

[Allen Browne]

The queries named ~sq... are for places where you used a SQL statement in
the RecordSource of a form/report or the RowSource of a combo/list box.

There is something else wrong if the same MDE complains about a non-existent
query on one machine, but it is not a non-existent query on another machine
(using the same MDE.)

 

[chris]

Yes, there must be something wrong.. unfortunately is a client in another
country...

I checked the dates of these queries and found that MDB version. They were a
rowsource of subforms + form which I deleted... but these queries were not
deleted...

I will try to delete these queries manually from MySysQueries & MySysObjects
and see the result...

 

[chris]

Unfortunately MySys... tables are read-only :-(

 

[Allen Browne]

Compact/repair the front end?

Or use a copy of the front end that doesn't have these?

In any case, there has to something else going on here, e.g. different
veresion of msjet40.dll, or regional settings, or ...