Error 3420 Object invalid or no longer set under Vista

[Alan]

I have an Access 2003 database running multiple queries that was written on
XP, where it works perfect. I recently installed Office 2003 under Vista.
One of the queries no longer works. I get the error Error 3420 Object
invalid or no longer set. If I view the object dependencies, it shows the
problem query is not supported, which then points to the Name AutoCorrect
feature. I get the same error whether this is enabled or disabled. What
could be wrong? This is the query that errors:

SELECT TOP 10 qryOverall_Totals_All_Classes.Dog_Name,
qryOverall_Totals_All_Classes.Owner, qryOverall_Totals_All_Classes.Points,
((SELECT COUNT(*) FROM qryOverall_Totals_All_Classes AS T WHERE T.Class = "A"
AND T.Points > qryOverall_Totals_All_Classes.Points)+1) AS Place
FROM qryOverall_Totals_All_Classes
WHERE (((qryOverall_Totals_All_Classes.Class)="A"))
ORDER BY qryOverall_Totals_All_Classes.Class,
qryOverall_Totals_All_Classes.Points DESC;


This is the query that the problem one reads from:

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.Dog_Name,
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results INNER JOIN qryPulls_Entered ON
qryAll_Results.ID=qryPulls_Entered.ID
WHERE qryPulls_Entered.CountOfID>3
GROUP BY qryAll_Results.Class, qryAll_Results.Dog_Name,
qryAll_Results.Owner, qryAll_Results.ID;


This one runs just fine. Essentially, this one just pulls all the data from
the database where an entrant has been in 3 or more competitions. The query
with the problem then reads from this dataset. There are no problems running
this under XP.

Any help would be appreciated.

 

[Allen Browne]

Alan, there's more to this than you posted: since your lower-level query
draws from 2 other queries, the problem could be lower down.

So, the problem could be occuring at many levels. You certainly want Name
AutoCorrupt turned off:
http://allenbrowne.com/bug-03.html
and you then need to compact the database. Then modify each of the queries
in some way (which forces Access to recompile them.)

If that doesn't solve it, you are performing lots of aggregation here. Are
you aggregating on any Yes/No fields, or any Memo fields? Grouping no yes/no
fields will cause you grief, and grouping on memos could cause problems if a
pointer has gone bad.

In the subquery, is there any chance of counting a particular field and
aliasing it, e.g.:
SELECT Count(T.Class) AS CountOfClass FROM ...
It sometimes helps JET if you give it a name and something specific to work
with.

Is there a difference in versions between the machine where it works and the
one where it doesn't? Same service pack of Office 2003? Same version of
msjet40.dll?

Does it make any difference on the Vista machine if you create a shortcut to
msaccess.exe, check the Run As Administrator box in the properties of the
shortcut, and start Access from there?

If these are attached tables from across the network, does it make any
difference if you copy the back end to the local hard drive, and use the
Linked Tables Manager to connect to the local file?

Do any of the queries call VBA functions? If so, are there any
differences/issues with library references:
http://allenbrowne.com/ser-38.html

 

[Alan]

Allen,

Tahnks for the suggestions. Sorry I didn't respond sooner. Life kind of
got in the way of things here, and I forgot about this...until I needed to
use it again.

I tried your suggestions. Both machines are running the same service pack
and version of jet. AutoCorrect is turned off (does not work with it on,
either). Database is local, nothing across a network. No VBA functions.
Run as Administrator and Run as XP SP2 did not work.

I did some experimenting, and I found the problem. I just don't know how to
fix it yet. Problem is in the ORDER BY statement of the problem query below
(Select Top 10...). If I remove the part
"qryOverall_Totals_All_Classes.Points DESC" from the ORDER BY statement, the
query works. But the results do not sort in the order I need them. The
query that produces the dataset this one reads from (query starting Select
Distinctrow... below) works fine on it's own. Vista appears to not like the
fact that the Points field is an aggregate field in the subquery, at least
when it comes to sorting.

I have no idea right now how to fix that, but I'm still experimenting. I
can post the database somewhere if someone thinks seeing it might help. File
size is 1.7M.

- Al

 

[Allen Browne]

Okay, you are making progress to pinning it down.

I don't understand the "object no longer set" message. Sometimes the JET
optimizer does not run the subquery to completion. If that is actually the
cause of the problem you can force it to materialize the subquery by using:
(SELECT TOP 100 PERCENT Count(ID) AS CountOfID
FROM qryOverall_Totals_All_Classes AS T
WHERE ...)

Of course the TOP 100 PERCENT should not be needed, but it sometimes helps.
Counting the primary key field instead of * may also help, even though it is
less efficient. And providing an alias for the count might also help
unconfuse JET.

Let us know how you go with this.

 

[Alan]

Well, so far, no progress. Still stuck on the ORDER BY clause throwing the
error. The top 100 percent didn't work, and neither did the aliasing. Still
researching, but I'm almost getting to the point of just building make-table
queries and tying them together with a macro.

 

[Allen Browne]

Alan, the earlier parts of this thread are disappearing from my server, so
I'm recapping the issue here.

Lower level query is saved as 'qryOverall_Totals_All_Classes':
SELECT DISTINCTROW qryAll_Results.Class,
qryAll_Results.Dog_Name,
qryAll_Results.Owner,
Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results INNER JOIN qryPulls_Entered
ON qryAll_Results.ID=qryPulls_Entered.ID
WHERE qryPulls_Entered.CountOfID > 3
GROUP BY qryAll_Results.Class,
qryAll_Results.Dog_Name,
qryAll_Results.Owner,
qryAll_Results.ID;

Query built on top of that is:
SELECT TOP 10 qryOverall_Totals_All_Classes.Dog_Name,
qryOverall_Totals_All_Classes.Owner,
qryOverall_Totals_All_Classes.Points,
((SELECT COUNT(*)
FROM qryOverall_Totals_All_Classes AS T
WHERE T.Class = "A"
AND T.Points > qryOverall_Totals_All_Classes.Points)+1)
AS Place
FROM qryOverall_Totals_All_Classes
WHERE (((qryOverall_Totals_All_Classes.Class)="A"))
ORDER BY qryOverall_Totals_All_Classes.Class,
qryOverall_Totals_All_Classes.Points DESC;

Problem: this works on some machines, but fails on one.
Observation: query works if you remove this in ORDER BY clause:
qryOverall_Totals_All_Classes.Points DESC

Name AutoCorrect is off.
Same version of msjet and Access, and same service pack.
Compact/repair did not help.

Suggestion:
========
The lower level query sums a field named Points that looks like it comes
from another query (one we don't know about), and then aliases it with the
same name (Points) again. Try changing the alias up through the stack.

If the lowest level query is aggregating, change the alias there, e.g. if it
groups by First of point, you might alias it as FirstOfPoints.

Then the next query would:
Sum(qryAll_Results.FirstOfPoints) AS SumOfPoints

And the top level query would:
ORDER BY ..., qryOverall_Totals_All_Classes.SumOfPoints DESC

It just might stop Access getting confused.

 

[Alan]

Hi Allen,

One correction. The problem is that it works under Windows 2000, and XP,
but not under Vista.

I did originally post the base query. Must have been dropped from the
thread. Here is the lowest (first) query that runs called qryAll_Results:

SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.Dog_Name, tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt,
tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt, tblResults.Time,
tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5)
AS Bonus,
Iff([Bonus]=00,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt

FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON
tblDogs.ID=tblResults.Dog_ID) ON tblEvents.Event_ID=tblResults.Event_ID) ON
tblClass.Weight=tblResults.Dog_Wt;

This one pulls a bunch of data from a table and makes the original points
calculation. I left this one aliased as "Points". Then I changed the next
one to "MidPoints" and followed it up through the chain. Got the same
"Object invalid or no longer set" error.

You'll notice (I missed this originally) that the middle query
'qryOverall_Totals_All_Classes' is joined to another dataset created by the
query 'qryPulls_Entered:

SELECT qryAll_Results.ID AS ID, qryAll_Results.Dog_Name,
qryAll_Results.Owner,
Count(qryAll_Results.ID) AS CountOfID
FROM qryAll_Results
GROUP BY qryAll_Results.ID, qryAll_Results.Dog_Name, qryAll_Results.Owner
ORDER BY qryAll_Results.Dog_Name;

That query totals the number of events each entrant has been in.

I did do something else that did work, though. The middle query
'qryOverall_Totals_All_Classes' I turned into a Make Table query, so it now
creates a table called 'tblTotalAllClasses'. I then changed the top-most
query (SELECT TOP 10...) to read from the table instead of the other queries.
This has the effect of reading a hard value for points instead of an
aggregate field. With a little VBA to drop the table then execute the
make-table query, this produces the desired result. I shouldn't need to do
this, but it works, and I'm going to run with it. I'm still really curious
as to why Vista generates that error when sorting on an aggregate field.
Something to keep researching in my "spare" time.

Thanks for all your help! If you think of anything else, I'll try it. Or I
could email you the database if you want to dig deeper.

Thanks again!

 

[Allen Browne]

Okay: you have a workaround.

I agree that the temp table should not be needed. I suspect that it is a way
of circumventing a problem due to data type or field names, but it works so
we will leave it at that.