Run Time Error 7965 when displaying recordset

[Robin9876]

In an ADP form that is calling a stored procedure with the parameter
string defined in VBA and the results displayed in a sub form. When
searching by name it works correctly, however when searching by a date
it displays the following error message.

Run-time error '7965':
The object you entered is not a valid recordset property.

This occurs on the set recordset line below.

Set rs = CreateObject("ADODB.recordset")
rs.CursorLocation = adUseClient

rs.Open "mySP " & ParamStr, conn

Set Me.mySubForm.Form.Recordset = rs


However using the generated parameter string from SQL Management
Studio the data was returned.

Does anybody know why it is working with some parameters yet when it
does not work in Access the generated SQL statement works within the
SQL Server tools?

 

[Sylvain Lafontaine]

When using parameters, you should always use a Command object and its
Parameters collection; something like:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

' Don't forget the SET at the beginning of this line; otherwise you will
create a second connection object
' using the connection string of the CurrentProject.Connection object:
Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "mySP"

IF (FALSE) THEN
' This block is only to show you the use of the .Refresh command
' but its use will generate round trips to the server:

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next
END IF

cmd.Parameters.Append cmd.CreateParameter("@MyIntegerValue", adInteger,
adParamInput, , MyIntegerValue)
cmd.Parameters.Append cmd.CreateParameter("@MyDateValue", adDBTimeStamp,
adParamInput, , MyDateValue)

rs.Open cmd, , adOpenStatic, adLockOptimistic

Finally, don't be confused by the fact that you are giving each parameter a
name like @MyIntegerValue and @MyDateValue, these names are only used
locally and won't necessarily follow the names of the parameters defined for
the SP; so you will have to closely follow the order of parameters when you
will create them. This mean that if your SP is returning a value, you must
add the following line at the very beginning of the list because the Return
value is always the first parameter of a SP:

cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue)

Finally, in your case, you might want to try adding the EXEC statement, I
don't know but *maybe* this will work:

rs.Open "EXEC mySP " & ParamStr, conn

 

[Robin9876]

Using the command and parameter method it still works when entering a
name to search but when entering a date is still fails but now with
the following error.

Run-time error '-2147467259 (80004005)':
Method 'Recordset' of object'_Form_mySubForm' failed


This occurs at the following line of code.
Set Me.mySubForm.Form.Recordset = rs

 

[Sylvain Lafontaine]

Did you check to see if the recordset "rs" was OK before trying to set
Me.mySubForm.Form.Recordset to it?

When executed from Management Studio, do you see any error message? Did you
forgot to put the SET NOCOUNT ON option at the beginning of the SP?

Maybe there is an error in your SP and the recordset is either empty or
closed. You should also take a look with the SQL-Server Profiler to see
what happens on the SQL-Server (ie, what commands are sent by Access).

 

[Robin9876]

The rs does not have anything after the rs.open but when the request
was captured in SQL Profiler and run in SQL Management Studio it
returned the data correctly.

It returned it in under 1 seconds, only 4 rows and 7 fields therefore
there is not much data so should not be timing out or reaching any
limit.

 

[Sylvain Lafontaine]

Add the SET NOCOUNT ON option at the beginning of the SP and see if there is
something in the rs after the rs.open after that.

 

[Robin9876]

Thanks this now works.

I had previously added it to the stop of the stored procedure but then
noticed it was not in the SP code when modifying it and therefore
added it to within the Alter statement.

What difference does this one statement do to the returned data?

 

[Sylvain Lafontaine]

When SET NOCOUNT is OFF, SQL-Server will return a closed resultset
containing the number of lines returned by each Select statement; even when
these Select statements don't return record to the client. This (these)
closed resultsets/recordsets is what you are seeing as an empty recorsdet.

Either set NOCOUNT to ON at the beginning of the SP (and to any trigger as
well that might get called) or get the next recordset using the
..NextRecordset method of the recordset.