Problem with QueryDefs in VBA
Rob Parker
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
Jack Leach
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)
Rob Parker
> 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!
>
>
Rob Parker
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
Jack Leach wrote:
> 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!
>
>
Marshall Barton
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.
--
Marsh
MVP [MS Access]
Rob Parker wrote:
>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.
>
>
>Jack Leach wrote:
>> 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.
>>
>>
Jack Leach
> I'll recognise as being suitably knowledgeable, such as one of the MVPs -
> that it will solve the problem.
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)
Rob Parker
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
Marshall Barton wrote:
> 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.
>
Rob Parker
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 Leach wrote:
>> or advice from someone who
>> I'll recognise as being suitably knowledgeable, such as one of the
>> MVPs - that it will solve the problem.
>
> 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.
>
>
>
Rob Parker
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 Parker wrote:
> 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
>
<snip>
Jack Leach
Gina Whipp
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
"Rob Parker" <NOrobppa...@optusnet.com.auFORME> wrote in message
news:%23f10JWR...@TK2MSFTNGP02.phx.gbl...
Rob Parker
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 Whipp wrote:
> 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.
>
>
<snip>
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>Access 2003 SP1;
SP1? Why not patch to SP3?
>Jet Version 4.0.9511.0
You're up to date there.
Given that you're on SP1 I'd suggest see if putting SP3 solves your
problem. If so you'll likely the following hotfix as well.
Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007
http://support.microsoft.com/kb/945674
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/
Rob Parker
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
Gina Whipp
What version of Access and what version of Windows? (i can't seem to find
that in previous postings.) Also, what References do you have set (and in
what order)?
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"Gina Whipp" <NotInt...@InViruses.com> wrote in message
news:%23lcjxUT...@TK2MSFTNGP06.phx.gbl...
AccessVandal via AccessMonster.com
and Track name AutoCorrect info when you import the objects in a new database
than do a compile and compact and repair?
Might not work, but at least give it a try.
Rob Parker wrote:
>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
--
Please Rate the posting if helps you.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200910/1
Rob Parker
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
Gina Whipp wrote:
> Rob,
>
> What version of Access and what version of Windows? (i can't seem to
> find that in previous postings.) Also, what References do you have
> set (and in what order)?
>
>
Rob Parker
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
Gina Whipp
When you imported all the objects into the new database, did you import the
MSysObjects and MSysQueries tables also? Another thought, when you compile
the code do you get any errors?
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"Rob Parker" <NOrobppa...@optusnet.com.auFORME> wrote in message
news:exRdhklR...@TK2MSFTNGP02.phx.gbl...
Rob Parker
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
Gina Whipp wrote:
> Rob,
>
> When you imported all the objects into the new database, did you
> import the MSysObjects and MSysQueries tables also? Another thought,
> when you compile the code do you get any errors?
>
>
Gina Whipp
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
"Rob Parker" <NOrobppa...@optusnet.com.auFORME> wrote in message
news:OhJiYfsR...@TK2MSFTNGP06.phx.gbl...
Stuart McCall
> 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.
And a more thorough method in this regard is to paste the following code
into a standard module and run it. The queries listed beginning with ~sq are
hidden queries attached to various objects like form controls.
Public Sub ListQdfs()
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
Debug.Print qdf.Name
Next
End Sub
My guess is that you'll turn up something in one of the ~sq items. When you
spot something suspicious, type this in the immediate window:
?CurrentDb.QueryDefs("QueryName").SQL
(replace the obvious)
Rob Parker
If you've been following this thread from the start, you would have seen
that what you are suggesting runs fine. It gives me a list of all the
queries (including the system/hidden ones, starting with ~). The problem is
that when I use the qry.Parameters.Count property - to list only those
queries which have a parameter - it falls in a heap, and says it can't find
a query which doesn't exist - and which does not appear in the list of
queries produced by the code you offered.
Rob
Rob Parker
I think your second suggestion here has nailed it. There is another query
in the db (one which I am almost certain is no longer used anywhere) which
uses the tblWA_ct query that no longer exists.
It seems that code which simply loops through the QueryDefs collection for
the names of queries does nothing else behind the scenes; but when it needs
to check each query's parameters it needs to open the query, and that's when
it fails.
Problem solved.
Thanks,
Rob
PS. Fortunately, the offending query was near the top of the list of about
300, so it wasn't too much of a pain ;-)
Gina Whipp wrote:
> 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
Glad we finely got that mystery cleared up (and glad it didn't take ALL
day)!
You're welcome...
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"Rob Parker" <NOrobppa...@optusnet.com.auFORME> wrote in message
news:%23YXM3m0...@TK2MSFTNGP04.phx.gbl...