Re: Error 3420 Object invalid or no longer set under Vista
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Fri, 15 Feb 2008 16:19:57 +0900
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.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Alan" <Alan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D121DE5-E2E2-4436-AE38-90F1C2677D59@xxxxxxxxxxxxxxxx
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" wrote:
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" <Alan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EDD84BA9-D6DB-4836-AD55-DF16739E3F4C@xxxxxxxxxxxxxxxx
>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 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.
.
- References:
- Prev by Date: Re: Eliminating repetitions in a field
- Next by Date: Re: NULL problem with Created table
- Previous by thread: Re: Error 3420 Object invalid or no longer set under Vista
- Index(es):
Relevant Pages
|
