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