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

Michael,

Parameterised stored procs can be tricky sometimes but if this was working
before I guess you know what you are doing. Do you get a recordcount on the
recordset returned? Or, are there multiple recordsets coming back rather
than just the one that you are anticipating? The reason I ask is that you
may be getting a series of results back that are something like

Recordset 1: 100 Rows Affected
Recordset 2: 10 Rows Affected
Recordset 3: What you were actually after closed, no recordcount available

If no and yes to these two questions, have a look at the stored proc and
make sure that you have the SET NOCOUNT ON option set at the start of the
proc. e.g.

CREATE proc spGetMAV3(@IndexVal smallint)
as
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
--rest of proc here
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
END

If you want to minimise lock time on the db, you could also disconnect the
recordset before you clear the sheet and copy the rs using
Set rst.ActiveConnection = Nothing

HTH,

Robin Hammond
www.enhanceddatasystems.com

"Michael Kellogg" wrote in message
48.16...
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

Hi Robin,

I can't really tell what, if anything, is coming back because the
recordset is closed. The last line of the sproc is a SELECT statement
and that, by itself, has worked with the ODBC method. However, your
suggestions are good ones and I will add those settings right away. It
seems like good housekeeping, regardless of whether it solves this
problem.

I did not know I could disconnect the Connection object and still have
access to the recordset in "classic" ADO. That is a nifty idea.

Thanks for your help,
Michael


"Robin Hammond" wrote:
Parameterised stored procs can be tricky sometimes but if this was
working before I guess you know what you are doing. Do you get a
recordcount on the recordset returned? Or, are there multiple
recordsets coming back rather than just the one that you are
anticipating? The reason I ask is that you may be getting a series of
results back that are something like

Recordset 1: 100 Rows Affected
Recordset 2: 10 Rows Affected
Recordset 3: What you were actually after closed, no recordcount
available

If no and yes to these two questions, have a look at the stored proc
and make sure that you have the SET NOCOUNT ON option set at the start
of the proc. e.g.

CREATE proc spGetMAV3(@IndexVal smallint)
as
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
--rest of proc here
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
END

If you want to minimise lock time on the db, you could also disconnect
the recordset before you clear the sheet and copy the rs using
Set rst.ActiveConnection = Nothing

HTH,

Robin Hammond
www.enhanceddatasystems.com

"Michael Kellogg" wrote in message
48.16...
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






--
Michael Kellogg