ADO recordset closed, cannot read

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have a module in Excel that downloads data from a SQL Server source and
puts it into the spreadsheet using the CopyFromRecordset command. When I
originally put it together, I was using an ADODB Recordset object but I
used an ODBC DSN for my connection (no idea why I did that). Anyway, it
worked just fine. However, recently I noticed the ODBC specification and
I changed it to a plain ADO connection instead. My connection still
works but for some reason, now when the data comes down the recordset
stays closed and thus I can't issue the CopyFromRecordset method. Here
is the relevant code:
-----------
Private Sub cmdImport_Click()
'Dimension all vars
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prior, fwd As ADODB.Parameter
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

'Initialize parameters
Set prior = cmd.CreateParameter("DaysPrior", adInteger, adParamInput,
, txtDaysPrior.Value)
Set fwd = cmd.CreateParameter("DaysForward", adInteger, adParamInput,
, txtDaysForward.Value)

'Initialize connection
On Error GoTo BadPwError
'old version (ODBC) commented out:
'cn.Open "DSN=app_prod;Uid=app_Prod;Pwd=" & ProdRpt_Password.Text
'new version (ADO):
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=app_Prod;Initial Catalog=Live;Data Source=LASQL;Password=" &
ProdRpt_Password.Text

'Initialize command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ProdDue_rev3"
cmd.CommandTimeout = 60
cmd.ActiveConnection = cn
cmd.Parameters.Append prior
cmd.Parameters.Append fwd

'Initialize recordset (run the query)
Set rst = New ADODB.Recordset
rst.Open cmd, , adOpenStatic, adLockReadOnly

'Clear the sheet and re-fill it with the new information
Me.UsedRange.Clear
Me.Cells(2, 1).CopyFromRecordset rst

'Clean up
rst.Close
cn.Close

End Sub
----------------

Can anyone tell me why this recordset won't open, or stay open? The
"rst.Open" command hangs for about the appropriate amount of time,
indicating the sproc IS running. But then the recordset stays closed. As
soon as I switch back to the other connection method, everything runs
fine again.

Thanks for any advice.

--
Michael
.

Relevant Pages

  • Re: More ASP.Net Newbie Questions
    ... The Command is then what you're doing with this connection, ... In regards to your final point, making grids and controls in general do ... > Connection and Recordset objects into, like, 37 different things. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: insert Q
    ... Use an explicit Connection object/ ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ... A Connection object implements the connection to the database. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Recordset Closed After Having Just Being Opened
    ... by 'pass the command object to a recordset as the source parameter'. ... You must set the command.ActiveConnection to an already-opened connection ...
    (microsoft.public.vb.database.ado)
  • Re: How to fill a Client Side Recorset with a command (sp exec)
    ... To be able to open recordset on a client side using Command, ... CursorLocation property of the Connection to adUseClient ... > command from a connection, but i´m having problems with the parameters. ...
    (microsoft.public.data.ado)
  • Debugger
    ... Transport dt_socket failed to initialize, ... Connecting/disconnecting my internet connection makes no difference. ... dt_socket on the command line however I altered the configuration. ...
    (comp.lang.java.softwaretools)