CurrentProject.Connection.Execute Error 3

[sgidley]

Hello, I have a seemingly simple command set up but I can't get it to work. Here is the pertinent code:

Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.Execute("SELECT qryTotal_Compliance_Data.* FROM qryTotal_Compliance_Data;")

I went ahead and copied the SQL right out of the Access SQL Design window. This query works fine form the Access UI, but when I try to access it this way, I get this Error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Any ideas what is going on?

Your help is much appreciated.

 

[RoyVidar]

You're opening a query, does it have parameters? If so, they need to be resolved.

In stead of the structure you're using, I'd probably do

Set rs = CurrentProject.Connection.Execute("qryTotal_Compliance_Data",,adCmdStoredProc)

Here's one old thread using the ADO Command object and resolving one parameter from a form thread705-1116203

The current recommendation from MS, is to use DAO.

Roy-Vidar

 

[sgidley]

hmmm, it looks like you were right in that my error message had to do with parameters. I didn't think that would be a problem... The Access query references some (hidden) text boxes on the main form and uses them as parameters. It works when I open the query with the Access UI, and I thought I was just opening the same query in code so that it would work, but for some reason it does not.

My goal is to open the query in code and see how many records it returns. To simplify things for now, I have removed all the parameters from the query. Normally this query returns 22,000 records with no parameters. In code, I have tried with both ADODB and DAO to use a recordset to see how many records I get form the query. With the ADODB method, I get a "-1" displayed in the textbox, and with the DAO method, I get "1". I would expect 22,000. Immediately following, I open the query with DoCmd, and of course all the records appear in the Access query viewer. Any ideas why I can't get the right recordcount? Here is the code for both cases:

ADODB:
Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.Execute("qryTotal_Compliance_Data", , adCmdStoredProc)
If rs.EOF Then
Set rs = Nothing
MsgBox "No Results"
Else
MsgBox "Here comes the recordCount"
MsgBox rs.RecordCount
DoCmd.OpenQuery "qryTotal_Compliance_Data"
End If

DAO Method:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTotal_Compliance_Data")

Set rs = qdf.OpenRecordset

If rs.EOF Then
Set rs = Nothing
MsgBox "No Results"
Else
MsgBox "Here comes the recordCount"
MsgBox rs.RecordCount
DoCmd.OpenQuery "qryTotal_Compliance_Data"
End If

 

[RoyVidar]

ADO gives recordcount under some circumstances (that is quite slow), but I've learned not to trust it anyway ;-)

For DAO to give recordcount, you'd need to "access" all records first, which means you need to give it a .movelast

MsgBox "Here comes the recordCount"
rs.movelast
MsgBox rs.RecordCount

Neither ADO nor DAO knows anything about Access objects (forms, text controls and suchlike), they are methods for retrieval and manipulation of data and data structure, so parameters needs to be resolved.

Roy-Vidar

 

[sgidley]

Thanks for the good information!

So About this...
<<Neither ADO nor DAO knows anything about Access objects
<<(forms, text controls and suchlike), they are methods for <<retrieval and manipulation of data and data structure, so <<parameters needs to be resolved.

Hmmm. So I'm thinking this is how it works... when I give the query's name: "qryTotal_Compliance_Data" to the DAO libraries, it can use all of the SQL Code in my saved query as long as it refers to Access tables and queries, but since I tried to use a parameter and refer to an Access Control (textbox), DAO chokes on it because it doesn't know what that is. By all means, correct me if I'm wrong!

 

[RoyVidar]

That's correct, so we need to resolve the parameters.

The thread I linked to in my first reply, gives one sample of doing so explicitly with one parameter with ADO (my sample), then below, how to do it dynamically in DAO (jw5107 sample), regardless of how many references to forms exists in the query.

Roy-Vidar

 

[PHV]

My goal is to open the query in code and see how many records it returns
The following should work, provided the parameter's form is open:
MsgBox DCount("*", "qryTotal_Compliance_Data")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sgidley]

Thanks PHV, that's probably what I was really looking for in the beginning, since my query is allready built up in Access anywyas, and yes the parameter's form is open. Using an Access function with one line of code instead of ADO with many... out of curiosity, does anyone know which method would be more efficient? In my case, I am changing a parameter over and over again until it finds results, so most of the queries return 0 records.

 

[bakershawnm]

Just wanted to say thanks to Roy for these valuable explanations. I had a similar situation however I was selecting from another query. In that query I had some unresolved parameters taken from the form that was open. I was able to find the issue and fix it.

thanks again and have a star