Re: Problem with QueryDefs in VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

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 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.


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.


.

Relevant Pages

  • Re: Problem with QueryDefs in VBA
    ... I'll recognise as being suitably knowledgeable, such as one of the MVPs - ... Perhaps importing all my database objects into a new database would work, ... For Each qry In CurrentDb.QueryDefs ...
    (microsoft.public.access.formscoding)
  • Re: Problem with QueryDefs in VBA
    ... I'll recognise as being suitably knowledgeable, ... MVPs - that it will solve the problem. ... Decompile only affects the VBA code, not the database itself. ... For Each qry In CurrentDb.QueryDefs ...
    (microsoft.public.access.formscoding)
  • Re: Problem with QueryDefs in VBA
    ... cause all kinds of weird problems so suggesting that it be ... Importing everything to a new database ... blindly importing everything may be too simple minded. ... For Each qry In CurrentDb.QueryDefs ...
    (microsoft.public.access.formscoding)
  • Re: Problem with QueryDefs in VBA
    ... Perhaps importing all my database objects into a new database would work, ... Turning off Track Name Autocorrect ... For Each qry In CurrentDb.QueryDefs ... I again get RTE 3078. ...
    (microsoft.public.access.formscoding)
  • Re: WARNING: Relationships not imported with data
    ... Importing relationships was selected, ... Good advice -- you more or less described my plan. ... > manually fix referential integrity. ... > database you are working with this could take time... ...
    (comp.databases.ms-access)