John Tompa
New member
- Local time
- Today, 20:25
- Joined
- Mar 14, 2008
- Messages
- 9
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:
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:
