Re: Problem with QueryDefs in VBA

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

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.


--
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" wrote:

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


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!


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



.

Relevant Pages

  • Re: Problem with QueryDefs in VBA
    ... blindly importing everything may be too simple minded. ... Decompile only affects the VBA code, not the database itself. ... but I'm loathe to try that I have some evidence - or advice ... 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: Sharepoint - where are the files?
    ... Independent Experts (MVPs do not work for MS) ... I understand about the database part, and it's *probably* okay to have it stored only in the database, I'll have to check into that with the powers-that-be. ... So then I set it to allow attachments, but the spreadsheet still opens in the browser window. ... You'll have to create 'Lists'. ...
    (microsoft.public.windows.server.sbs)