Problem with QueryDefs in VBA

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I'm attempting to get a list of all queries in my database which have
parameters, via the following:

Dim qry As DAO.QueryDef

For Each qry In CurrentDb.QueryDefs
If qry.Parameters.Count > 0 Then
Debug.Print qry.Name
End If
Next qry

This fails, with RTE 3078 (MS Jet database engine cannot find query
'qryOrders_Arisings')

If I omit the inner If statement, and run the following, I get (as
expected) a list of all the queries:
For Each qry In CurrentDb.QueryDefs
Debug.Print qry.Name
Next qry

This list does not include qryOrders_Arisings (which was previously in
the database, but was deleted ages ago).

If I change the code to:
For Each qry In CurrentDb.QueryDefs
Debug.Print qry.Name; qry.Parameters.Count
Next qry
I again get RTE 3078.

I've done a compact/repair; I've included
CurrentDb.QueryDefs.Refresh
at the top of my code; I've tried
CurrentDb.QueryDefs.Delete("qryOrders_Arisings")
which gives RTE 3265 (Item not found in this collection) - not
unsurprisingly.

What's going on here, and how can I fix it?

TIA,

Rob
 
You can try:

Turning off Track Name Autocorrect
Importing everything into a new db or using the Decompile switch


I would think that a compact and repair would fix the issue, but if you have
Name Autocorrect on maybe not.

good luck!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Jack said:
You can try:

Turning off Track Name Autocorrect
Importing everything into a new db or using the Decompile switch


I would think that a compact and repair would fix the issue, but if
you have Name Autocorrect on maybe not.

good luck!
Click to expand...
 
Hi Jack,

Thanks for the response, but (to me) it seems that you've as little idea of
what's happening as I have.

I never have "Name AutoCorrupt" turned on in any of my databases, and I
can't conceive of how this might be of any influence to this problem. And
(as I understand it - please correct me if I'm wrong) Decompile only affects
the VBA code, not the database itself. And, as I said in my original post,
compact/repair failed to fix the problem.

Perhaps importing all my database objects into a new database would work,
but I'm loathe to try that I have some evidence - or advice from someone who
I'll recognise as being suitably knowledgeable, such as one of the MVPs -
that it will solve the problem. There is nothing else happening with this
database that leads me to think it is corrupted.

Rob
 
I think I have to agree with Jack here. NameAutoCorrect can
cause all kinds of weird problems so suggesting that it be
turned off is always a valid suggestion. Since you said
that you already have it turned off, that won't cure this
problem.

You're right about decompiling not affecting things outside
of modules. However, since you are having a problem in a
VBA procedure, it a reasonable and easy thing to do.

The fact that something shows up in the QueryDefs
collection, but Access can not find the object is a pretty
clear indicator that there is some kind of corruption
somewhere in there. Importing everything to a new database
is usually a relatively simple way to leave the problem
object behind.

Sometimes a bad object will come across in the Import so
blindly importing everything may be too simple minded. You
can be sure to leave it out by not using the select all
import option. Instead, select each query to import
individually, skipping the bad query. Note that I have had
trouble getting a good import when I used the select all
option and then unchecked the bad object. I have also seen
cases where the bad object did not show up in the list so it
could not even be unselected.
 
or advice from someone who
I'll recognise as being suitably knowledgeable, such as one of the MVPs -
that it will solve the problem.
Click to expand...

Well maybe next time you can post your question as

MVP's ONLY!!!

[question]



It's not all that hard to make a copy of the db to try it... and just
because you don't see why certain things might work, doesn't mean others
don't also... mvp's or not.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Thanks Marsh,

Seems like an import to a new db is worth trying. And while I appreciate
the advice about not selecting the bad object for import, that won't be a
problem because it doesn't exist! I'll try it when I get to work later
today and post the result.

Rob
 
Hi Jack,

Sorry for any offence - none intended. And I do recognise that people other
than MVPs can post valuable advice - I see lots of it in these groups. What
I was hoping for was an explanation of what has happened.

Rob

Jack said:
or advice from someone who
I'll recognise as being suitably knowledgeable, such as one of the
MVPs - that it will solve the problem.
Click to expand...

Well maybe next time you can post your question as

MVP's ONLY!!!

[question]



It's not all that hard to make a copy of the db to try it... and just
because you don't see why certain things might work, doesn't mean
others don't also... mvp's or not.


Hi Jack,

Thanks for the response, but (to me) it seems that you've as little
idea of what's happening as I have.

I never have "Name AutoCorrupt" turned on in any of my databases,
and I can't conceive of how this might be of any influence to this
problem. And (as I understand it - please correct me if I'm wrong)
Decompile only affects the VBA code, not the database itself. And,
as I said in my original post, compact/repair failed to fix the
problem.

Perhaps importing all my database objects into a new database would
work, but I'm loathe to try that I have some evidence - or advice
from someone who I'll recognise as being suitably knowledgeable,
such as one of the MVPs - that it will solve the problem. There is
nothing else happening with this database that leads me to think it
is corrupted.

Rob
Click to expand...
Click to expand...
 
Follow-up information:

I imported all my objects into a new database (set with "Auto Corrupt"
turned off). Ran my code, and got an error message due to another query.
Imported all objects except for that query from the new db into another new
db, and then got error that the query which gave error in first import was
missing. Seemed this is likely to go on forever, so I gave up and did some
productive work - haven't got the luxury of chasing pseudo-gremlins in work
time.

But it's intriguing. Any further thoughts from you or Jack, or anyone else
who may be following this thread.

Rob

PS. Tried the same code in my test database, which I use for checking out
questions from these groups; got RTE 3078 from a query which has been
deleted when I used the qry.Parameters.Count property in the code loop; that
query didn't show in a simple loop to print all query names. I suspect
there's something seriously amiss somewhere.


Rob said:
Thanks Marsh,

Seems like an import to a new db is worth trying. And while I
appreciate the advice about not selecting the bad object for import,
that won't be a problem because it doesn't exist! I'll try it when I
get to work later today and post the result.

Rob
Click to expand...
<snip>
 
Rob,

You said you got a message due to another query... Is the message the same
and when do you get this message? Is this query part of another query or in
some code behind a form? What do you have *running* when you first open the
database, ie Main Menu? Also, didn't see (but I could missed that) what
version of Access.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi Gina,

Here's a blow-by-blow description of what's happening:

Access 2003 SP1; Jet Version 4.0.9511.0; Windows XP Pro
NameAutoCorrupt always turned OFF

There are essentially two versions on code, one which prints all query
names, and another which prints only names of queries which have any
parameters. I'll call them A and B for simplicity later on. Omitting
declarations, etc, which are as in my first post, the code fragments are:

A:
For Each qry In CurrentDb.QueryDefs
Debug.Print qry.Name
Next qry

B:
For Each qry In CurrentDb.QueryDefs
If qry.Parameters.Count > 0 Then
Debug.Print qry.Name
End If
Next qry

I'm running this from the immediate window, with no forms open and no other
code running.

And a couple of other points, which I've established by starting from
scratch with a new db, creating a single table and three queries based on
it, one with no parameters, one with an undeclared parameter, and one with a
declared parameter. For this db, Code A and Code B both run OK, and code B
reports 2 parameter queries (ie. it doesn't matter whether the parameter is
declared or not). Deleting a query (one of the parameter queries) and
re-running the code gives the same result as when first run; compacting the
database and re-running the code reports 2 queries and 1 parameter query
(ie. the QueryDefs collection contains deleted queries until after a
compact/repair).

Original database:
Code A runs OK
Code B gives RTE 3078 (Microsoft Jet database engine cannot find ...
'qryOrders_Arising' ...) ; this is not surprising since it's been deleted,
and several Compact/Repair operations run since then.

Following advice from Jack/Marsh, imported all tables, queries and modules
(needed because some queries reference UDFs) into a new database (db1):
Code A gives RTE 3070 (Microsoft Jet database engine does not recognise
'tblWA_ct.CA' as a valid field name or expression). There is a query
'tblWA_ct" (yes, the naming convention went west when the original developer
created that one - purely for development purposes, it was never used in
anger) in the database, with the following SQL:
TRANSFORM First(tblWA.WANumber) AS FirstOfWANumber
SELECT tblWA.CAWP, Last(tblWA.WANumber) AS WA
FROM tblWA
GROUP BY tblWA.CAWP
PIVOT tblWA.Task;
Originally, tblWA was joined to another table which had a field CA in it,
but this has since been removed, and the query modified accordingly;
originally, the CA field was in the query.

Deleted query 'tblWA_ct' from db1, and compact repair.
Code A runs OK
Code B gives RTE 3078 (Microsoft Jet database engine cannot find ...
'tblWA_ct' ...); yes, I've just deleted it and compact/repaired the db.

At this stage yesterday I gave up. But now I'm perservering and heading
onwards. Created a new database db2 and imported all objects from db1
(twice - see following).
Code A runs OK
Code B gives RTE 3078 (Microsoft Jet database engine cannot find ...
'tblWA_ct' ...). And it doesn't matter whether I import the queries by
"Select All" in the import dialog, or whether I select them individually
(which took a while, there's about 250 of them), as per Marsh's suggestion.

Compact/repaired db2.

Code A runs OK
Code B still gives RTE 3078 (Microsoft Jet database engine cannot find ...
'tblWA_ct' ...).

Can you - or anyone else following this thread - help?

Rob


Gina said:
Rob,

You said you got a message due to another query... Is the message
the same and when do you get this message? Is this query part of
another query or in some code behind a form? What do you have
*running* when you first open the database, ie Main Menu? Also,
didn't see (but I could missed that) what version of Access.
Click to expand...
<snip>
 
Hi Tony,

Thanks for the interest. I've updated to SP3, plus the post-SP3 hotfix, and
the problem remains exactly the same. I'm getting rather tired of importing
objects from one database to another ;-) I've got no idea how long it would
take to get rid of this strange corruption - but I'm puzzled as to why the
standard steps to remove corruption are failing for this db. Any further
thoughts on that?

Rob
 
Hi Gina,

That info was in the long descriptive post of 25-09-09. Following Tony's
response today I updated the Access SP, so the answers is:

Windows XP Pro, SP2
Access 2003, SP3 plus post-SP3 hotfix
Jet 4.0.9511.0

References are:
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library

Rob
 
Don't bother asking. I ALWAYS turn "NameAutoCorrupt" off in every database.
And no, I'm not about to turn it on and start experimenting - I'm trying to
get rid of corruption, not increase it ;-)

Rob
 
Hi Gina,

I don't normally have the "Show System Objects" option turned on, so the
MSys... tables were not available for selection. A quick test now shows
that, under those conditions, selecting via the "Select All" on the import
dialog does not copy the MSys... tables. And further testing shows that, if
"Show System Objects" is turned on and the MSys... tables are selected and
imported, the imported tables are renamed with a "1" suffix. Since I don't
have such tables in my series of imported databases, then I certainly
haven't imported those tables.

As for the code, there are no compile errors (even after decompiling).

Rob
 
Rob,

I have another thought...

ONLY do this on a BACK-UP COPY!!!

After making the BACK-UP, in the BACK-UP copy, uncheck all the References
that will allow you to do and then close the database. Open the database
and try running it again.

Another thing to do and this will be a pain but open every query (and maybe
unused forms, if there are any) and make sure that query isn't hanging
around somewhere. I know you are sure but it must be lurking somewhere.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Back
Top