Excel VBA executing SQL Server stored procedure - result set throwing error 3704

Question

I am trying to execute a SQL Server stored procedure from Excel VBA. The procedure returns rows into a result set object. However, while running the code, it throws an error:

3704 Operation is not allowed when the object is closed

Note:
There is no problem with the database connection because Select query running on the same connection object are working fine.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As New ADODB.Recordset

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
ThisWorkbook.initialize
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = ThisWorkbook.server
cn.Properties("Initial Catalog").Value = ThisWorkbook.db
cn.Properties("User ID").Value = "xxxxx"
cn.Properties("Password").Value = "xxxxx"
cn.Open

Set cmd = New ADODB.Command
cmd.CommandText = "Generate_KPI_Process_Quality_Check_RunTime"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn

Set prm = cmd.CreateParameter("@currentMonth", adChar, adParamInput, 255, cmb_month.Value)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@center", adChar, adParamInput, 255, cmb_center.Value)
cmd.Parameters.Append prm

rst.CursorType = adOpenStatic
rst.CursorLocation = adUseClient
rst.CursorLocation = adUseServer
rst.LockType = adLockOptimistic

rst.Open cmd

If (rst.BOF And rst.EOF) Then
'Some Code
End If

Hi @jerussell, It throws an error at the If (rst.BOF And rst.EOF) Then... statement at the end of the Code segment. — Abhishek Panda
– Abhishek Panda, Commented Aug 28, 2013 at 12:48
@Abhishek Panda Did any of these answers fix your problem? If so, upvote and/or mark as answered. — Tarik
– Tarik, Commented Aug 28, 2013 at 13:14
I am positive that you need to use the set keyword though or delete the first statement and use the solution proposed by @mehow — Tarik
– Tarik, Commented Aug 28, 2013 at 14:32

nzeemin · Accepted Answer · 2014-03-06 17:48:47Z

3

Put

SET NOCOUNT ON

in the stored procedure -- this will prevent output text generation like "1 record(s) updated".

edited Mar 6, 2014 at 17:48

answered Mar 6, 2014 at 16:00

nzeemin

9918 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Corley Brigman Over a year ago

welcome to stackoverflow! this answer is a little terse, can you give a little explanation to how this solves the problem?

nzeemin Over a year ago

Corley, looks like stored procedure output interfering with result recordset so OLEDB provider can't receive the recordset. SET NOCOUNT ON prevents output text generation on INSERTs/UPDATEs and so on.

Riley Major Over a year ago

Another question asks about this same error (in VB6) and SET NOCOUNT was the suggested answer.

user2140173 · Accepted Answer · 2013-08-28 12:55:30Z

1

You have to provide more parameters for the Open method of Recordset Object

try rst.Open cmd, cn

answered Aug 28, 2013 at 12:55

user2140173

4 Comments

Tarik Over a year ago

The connection object is an optional argument. It might fix the problem caused by the incorrect assignment "cmd.ActiveConnection = cn" though

Tarik Over a year ago

Indeed, it would work. However, if he decides to adopt your solution, he would better cleanup his code by deleting the offending statement that would potentially become a source of confusion in the future.

Abhishek Panda Over a year ago

@mehow - I tried your solution and I get this: Run-time error '3707': Cannot change the ActiveConnection property of a Recordset object which has a Command object at its source

user2140173 Over a year ago

@AbhishekPanda have you tried command.execute method?

Tarik · Accepted Answer · 2013-08-28 13:05:30Z

1

Use the Set keyword to assign the object:

Set cmd.ActiveConnection = cn

otherwise, the default property of the Connection object (which happen to be the connection string) will be assigned in lieu of the Connection object itself.

edited Aug 28, 2013 at 13:05

answered Aug 28, 2013 at 12:59

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

2 Comments

Abhishek Panda Over a year ago

Thanks for the help. However, that didn't work. It's still the same old 3704 error.

Tarik Over a year ago

I am positive that this is a problem and that you have to use set or delete the whole statement and use @mehow solution.

Kris van der Mast · Accepted Answer · 2015-09-23 14:55:08Z

0

Just put another recordset that will contain resultsets

Dim rst1 As New ADODB.Recordset
SET rst1=rst.NextRecordset 'this will return the first resultset

If rst1.BOF or rst1.EOF Then...
    'some code
End If

edited Sep 23, 2015 at 14:55

Kris van der Mast

16.6k8 gold badges41 silver badges65 bronze badges

answered Sep 23, 2015 at 14:50

mishimam

1

Collectives™ on Stack Overflow

Excel VBA executing SQL Server stored procedure - result set throwing error 3704

4 Answers 4

3 Comments

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related