Access runtiume errors 3078 and 7874 (1 Viewer)

Hello
I have an error message counting records in a query, or opening the query, using vba. The query runs correctly when executed manually when no VBA is running. I’m fairly sure this code ran a year ago as it is a once a year execution.
The code is intA = DCount("*", "qryAdh1EmailAppel") in the middle of a sub
To simplify finding the problem, I have written a small sub:
Private Sub tstQueries1()
Debug.Print DCount("*", "qryAdh1EmailAppel")
End Sub
The execution stops on the Debug line with the message “Runtime Error 3078. The Microsoft Office Access database engine cannot find the input table or query ‘qryAdh1EmailAppel’. Make sure it exists and that its name is spelled correctly”
The query exists and the name is spelled correctly. If I press Debug to interrupt the process, switch to the manual window and try to execute the query I get the message “Unknown Access database engine error”. I press OK & return to the vba code, press F8 to continue, get the same message & now press End. I go back to the Access window & the query executes correctly.
The query has the following SQL: SELECT * FROM qryAdhPrincA1 WHERE EMail Is Not Null AND ynUsage1=True;
If I change the sub to reflect the SQL of the query
Private Sub tstQueries2()
Debug.Print DCount("*", "qryAdhPrincA1", "EMail Is Not Null AND ynUsage1=True")
End Sub
Then it works correctly.
If I try to open the query instead of counting records:
Private Sub tstQueries3()
DoCmd.OpenQuery ("qryAdh1EmailAppel")
End Sub
Then it stops on the OpenQuery with the message: “Run-time error ‘7874’: Microsoft Office Access can’t find the object ‘qryAdh1EmailAppel’.”
I get the same error on executing manually as above.
This error occurs also on similar queries, but I haven’t detected a pattern.
The query is a based on a series of simple queries that I use elsewhere in the application. All the higher level queries work correctly in VBA & of course manually. The SQLs are (where I have taken out the brackets () for lisibility). I wonder whether the problem I due to the depth of cascading queries.
SQL of qryAdh1EmailAppel
SELECT * FROM qryAdhPrincA1 WHERE EMail Is Not Null AND ynUsage1=True;
SQL of qryAdhPrincA1
SELECT * FROM qryAdhPrincAx WHERE yAnnee=2011;
SQL of qryAdhPrincAx
SELECT *, yAnnee, ynRenouvellera FROM qryAdhPrinc INNER JOIN tblLicences ON qryAdhPrinc.[idAdherent] = tblLicences.[lnkAdherent] WHERE qryAdhPrinc.ynEpoux=No AND qryAdhPrinc.ynFamille=No AND tblLicences.strLicenceNb Is Not Null));
SQL of qryAdhPrinc
SELECT * FROM qryAdh WHERE ynEpoux=No;
SQL of qryAdh
SELECT *, IIf([ynepoux],[lnkAdherent],[idAdherent]) AS idAdhPrinc FROM tblAdherents ORDER BY Nom,Prenom;
I am using Access 2007 in Windows 7. All the updates have been included automatically.

:banghead:

Youmaywanttothrowalinebreakintherehereandthere.Itkindofmakesthingsmucheasiertoread

Hello
Sorry about that. It read OK on my PC. Hope this is better

Hello
I have an error message counting records in a query, or opening the query, using vba. The query runs correctly when executed manually when no VBA is running. I’m fairly sure this code ran a year ago as it is a once a year execution.


The code is intA = DCount("*", "qryAdh1EmailAppel") in the middle of a sub


To simplify finding the problem, I have written a small sub:The execution stops on the Debug line with the message “Runtime Error 3078. The Microsoft Office Access database engine cannot find the input table or query ‘qryAdh1EmailAppel’. Make sure it exists and that its name is spelled correctly”


The query exists and the name is spelled correctly. If I press Debug to interrupt the process, switch to the manual window and try to execute the query I get the message “Unknown Access database engine error”. I press OK & return to the vba code, press F8 to continue, get the same message & now press End. I go back to the Access window & the query executes correctly.


The query has the following SQL:
If I change the sub to reflect the SQL of the queryThen it works correctly.
If I try to open the query instead of counting records:Then it stops on the OpenQuery with the message: “Run-time error ‘7874’: Microsoft Office Access can’t find the object ‘qryAdh1EmailAppel’.”
I get the same error on executing manually as above.


This error occurs also on similar queries, but I haven’t detected a pattern.


The query is a based on a series of simple queries that I use elsewhere in the application. All the higher level queries work correctly in VBA & of course manually. The SQLs are (where I have taken out the brackets () for lisibility). I wonder whether the problem I due to the depth of cascading queries.


SQL of qryAdh1EmailAppelSQL of qryAdhPrincA1SQL of qryAdhPrincAxSQL of qryAdhPrincSQL of qryAdhI am using Access 2007 in Windows 7. All the updates have been included automatically.

If errors don't make sense then sometimes it is a sign of corruption. I'd make a new blank db and import all objects into it.

Perhaps other readers have other or better ideas.

At any point in your process, are you deleting and recreating the query in code or otherwise?

Hello vbaInet
No, I'm not recreating or modifying the query, only trying to execute it

Hello spikepl
That's a good idea, making a blank db & copying everything into it - I was hoping to avoid it! I'll have to get some quiet time & do it

thank you both

Best
John

Be careful now: I did not say copy into the new db, but in the new db, IMPORT from the old one. Those two processes are not equivalent, so use the IMPORT one.

John Tompa
I don't know about later versions but in A2003 the Import wizard can import everything in just a minute or two.

Hello spikepl
Thanks for that. I'm away for a few days & then I'll give it a try. I'll let you know what happens
Best
John Tompa

Yes from your code I can see that you're not deleting or modifying but what I asked was if you are EVER doing it somewhere in your entire project? And are you sure the name is qryAdh1EmailAppel and not qryAdh1EmailApple?

If the answers to the above are no then I guess we will know when you upload your db.

@vbaInet . . an apple being a pomme in French, I think OP would have used qryAdh1EmailPomme if that was what he meant

spikepl said:

@vbaInet . . an apple being a pomme in French, I think OP would have used qryAdh1EmailPomme if that was what he meant

Click to expand...

Haha!

Hi
To answer the serious question first. No I don't modify the query anywhere in the project in the normal run of the mill. It hasn't changed for some time now. And the name is spelled correctly - and anyway even if it weren't why would the query fail in manual mode when the VBA is stopped on the error? When other queris work.

Nuff serious stuff!

And no it's not apple! Appel means call & this query is a call for all Subscriptions for our club for those members (Adherents) who have an email last year (Year -1 hence the 1)

Best

John Tompz

Have you tried what spikepl advised in post #4 anyway?

Hello

I have tried opening a blank database & copying everything in using the Wizard. And unfortunately the error is still there, completely unchanged.
Everything else (well at least the few samples I did) seems to work correctly.

Thank you for the idea. I should have tried it sooner but I was worried it would be more complex.

Does it sound like an Access error? Should I report it to MicroSoft?

best

John Tompa

Can you upload a cut down version of your db?

Hello

I did indeed cut down my db to isolate the problem which did not go away. But I cut it down from the version I had imported.

So I started again from scratch. I created a new db & then imported everything with the wizard. I took my time over it, checking as I went along that nothing untoward was happening. And now the problem has vanished! So I'm not looking any further going by the old adage - if it's not broken, don't fix it.

Many thanks for your help & suggestions

John Tompa

Good to hear you're back up and running John.