Re: Problem with QueryDefs in VBA

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

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



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