Executing command - error record set is closed

[Neil]

Hello:

I am writing some VBA code in Access2003 to retrieve records from an
ADODB Command
calling a stored procedure on SQL Server 2000. I have verified the
stored procedure by manually calling it from Query Analyzer.

Here is a snippet of my VBA code:

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection /* This is an already open
connection */

'Get all of the properties that we are going to edit
Dim MyCommand As ADODB.Command

Dim Year As ADODB.Parameter
Dim propertyId As ADODB.Parameter

Set MyCommand = New ADODB.Command
With MyCommand
.ActiveConnection = cn
.CommandText = "MyDb.dbo.usp_GetProp"
.CommandType = adCmdStoredProc
End With

Set Year = New ADODB.Parameter
With Year
.Name = "inYear"
.Type = adSmallInt
.Direction = adParamInput
.Value = Forms!frmMain![txtYID].Value
End With
MyCommand.Parameters.Append Year

Set propertyId = New ADODB.Parameter
With propertyId
.Name = "inPropertyId"
.Type = adInteger
.Direction = adParamInput
.Value = Forms!frmMain![txtPropertyId].Value
End With
MyCommand.Parameters.Append propertyId

Dim rst As New ADODB.Recordset
Set rst = MyCommand.Execute

Do While Not rst.EOF
MsgBox "Top of loop", vbInformation, "Testing"

rst.MoveNext
Loop

Before I get into to loop, I am getting an error:

Operation is not allowed when the object is closed.

I tried adding an rst.Open before the loop to no avail.

Any ideas?

Thanks,
Neil

 

[Barry-Jon]

Is the connection open? You could test for this by evaluating cn.State

e.g. If cn.State <> adStateOpen then
cn.open
end if