Excel and ADO Run-Time Error 3704

[DRJD]

Hi all.

I'm trying to run a stored procedure on SQL server from Excel, something which I have done many times before. In this instance however, when I try to copy the contents of the recordset to the target range (using the Range.CopyFromRecordSet method) I get the following error message:

Run-time error '3704':

Operation is not allowed when the object is closed.

Now, I am fairly certain that the problem is that the problem is that the stored procedure uses a couple of SELECT ... INTO statements to create some temporary tables which the main SELECT is then run on the joined temporary tables, and therefore returns more than one recordset as each SELECT ... INTO staement generates a recordset (I think!).

From reading through the first couple of hits on Google, it would appear that I need to loop through the different recordsets but I am not sure how to do this.

Is anyone able to shed some light on this problem?

Thanks

[vba]Sub ADO(sRef As String, sWS As String, sServer As String, sDatabase As String, sUsername As String, sPassword As String)
Dim rg As Range
Dim rst As ADODB.Recordset
Dim sConn As Variant
Dim sSQL As Variant
Dim i As Long

'Set target range
Set rg = Worksheets(sWS).Range("A2")
'Create a new recordset object
Set rst = New ADODB.Recordset

'Connection Details
sConn = "Provider=SQLOLEDB;Server=" & sServer & ";Database=" & sDatabase & ";User ID=" & sUsername & _
";Password=" & sPassword & ";Trusted_Connection=False"

sSQL = "EXEC sp_ADOExport @REF = '" & sRef & "'"

'Open and query recordset
rst.Open sSQL, sConn

'Set column headers
For i = 0 To rst.Fields.Count - 1
Worksheets(sWS).Cells(1, i + 1).Value = rst.Fields(i).Name
Next i

'Copy recordset to target range
rg.CopyFromRecordset rst

'Close the recordset
rst.Close

'Clean Up
Set rst = Nothing
Set rg = Nothing[/vba]
Edit: Out of interest, here is the type of stored procedure that the sub is calling...

[vba]

CREATE sp_ADOExport

@REF As VarChar(50)

As

SELECT column1, column2
FROM table1
INTO #temp1
WHERE column1 = @REF

SELECT column3, column4
FROM table2
INTO #temp2
WHERE column3 = @REF

SELECT *
FROM table3
INNER JOIN #temp1
ON table3.column5 = #temp1.column1
INNER JOIN #temp2
ON #temp1.column1 = #temp2.column3
[/vba]

[Kenneth Hobs]

To iterate the recordset, see what I did here.
http://www.vbaexpress.com/forum/showthread.php?p=167033

[DRJD]

I think I may have misled you when I said that I needed to loop through the recordsets. What I meant was that I need to be able to use the range.CopyFromRecordset method for each recordset in turn. (Having said this I am not 100% sure that this is the problem).

Basically, what happens is between this piece of code [VBA] rst.Open sSQL, sConn [/vba] and ths piece [vba]rg.CopyFromRecordset rst [/vba] it appears that the recordset is being closed. I think that this is because the stored procedure that I am calling uses some SELECT ... INTO statements and then a final SELECT statement, meaning that it returns more than one recordset and , I guess, closes the other ones as it finishes with them. I am only interested in the results of the final query i.e. the final SELECT statement.

Does anyone have any idea how to get round this problem? (Other than rewriting the stored procedure).

[Kenneth Hobs]

I don't use SQLserver so I can't check your sql string.

You might try using the actual stored procedure via Tools > Import External Data. You can record a macro to get started. Here is an example where I used a stored procedure in an MDB.
[VBA]Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs

'Clear previous data
Cells.Delete

InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination

'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
, """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBatab" _
, "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
, "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Co" _
, "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
.CommandType = xlCmdTable
.CommandText = Array(cmdText)
.Name = dbName
.FieldNames = bFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = mdbPath
.Refresh BackgroundQuery:=False
End With
End Sub

[/VBA]

[DRJD]

For some reason I am unable ot make this work.

The main problem still is that the recordset closes itself immediately after being opened.

Can anyone shed any light on this? Does anyone know if there is a way round it?

I've tried executing the stored procedure as three seperate commands, but the commands involve using temporary tables on the SQL Server which seems to disappear befire I can requery them.

[6th]

For some reason I am unable ot make this work.

The main problem still is that the recordset closes itself immediately after being opened.

Can anyone shed any light on this? Does anyone know if there is a way round it?

I've tried executing the stored procedure as three seperate commands, but the commands involve using temporary tables on the SQL Server which seems to disappear befire I can requery them.

I had the same problem recently, try:

ALTER sp_ADOExport 
 
@REF As VarChar(50) 
 
As 
SET NOCOUNT ON
 
 SELECT column1, column2 
 FROM table1 
 INTO #temp1 
 WHERE column1 = @REF 
 
 SELECT column3, column4 
 FROM table2 
 INTO #temp2 
 WHERE column3 = @REF 
 
SET NOCOUNT OFF
 
SELECT * 
FROM table3 
INNER JOIN #temp1 
ON table3.column5 = #temp1.column1 
INNER JOIN #temp2 
ON #temp1.column1 = #temp2.column3

[geekgirlau]

This resolved the problem I've been having with a couple of stored procs