Recordset Error

[scking]

I've been working with ADODB for years but found one today that has me perplexed. I connect to a SQL Server database from an Access ADP file. The stored procedure works from Query Analyzer, returning >2800 rows. When I execute the command object and open the recordset I get no errors during debugging. However, when I try to .MoveFirst on the recordset I get an error saying it is an invalid action on a closed recordset. Now, I just opened the recordset two lines earlier successfully. I've also added an OUTPUT parameter in the T-SQL stored procedure that returns the count(*) of records in the table which is returned in the next line. In debug the variable shows > 2800 rows. I've been unsuccessful trying to find this and would appreciate any help you could provide.

I've checked the parameters being fed into the command object to ensure they are the correct ones. The co-stored procedure works.


Code calling the stored procedure

With oCmd
.CommandTimeout = 300
' set the SP to use for population of the recordset
If InStr(UCase(Filename), "ENHANCED") > 0 Then
.CommandText = "spCreateScheduleReport_ByMdsWParm_Enhanced"
ElseIf InStr(UCase(Filename), "OIARESULTS") > 0 Then
.CommandText = "spCreateScheduleReport_ByMdsWParm_OiaResults"
End If
.CommandType = adCmdStoredProc
.ActiveConnection = oCnn

oParm1.Direction = adParamInput
oParm1.Name = "@Mds"
oParm1.Type = adVarChar
oParm1.Direction = adParamInput
oParm1.Size = 65
oParm1.Value = MDS
.Parameters.Append oParm1

oParm2.Direction = adParamInput
oParm2.Name = "@ItemType"
oParm2.Type = adInteger
oParm2.Size = 4
oParm2.Value = ItemType
.Parameters.Append oParm2

oParm3.Direction = adParamOutput
oParm3.Name = "@Results"
oParm3.Type = adInteger
oParm3.Size = 4
oParm3.Value = Null
.Parameters.Append oParm3
.Execute
End With

If oParm3 <> 0 Then
Call AppMsgBox("Error processing schedule report.", _
"Please correct all schedule items found using" _
& "spCheckSchedule and rerun this report.", vbOKOnly)
GoTo Exit_Proc
End If
Call rstMpx.Open(Source:=oCmd, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly)

With rstMpx
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
strTextLine = !Task
pstrTextFile = pstrTextFile & strTextLine & vbCrLf
.MoveNext
DoEvents
Loop
End If
End With

---------------------
scking@arinc.com
---------------------