PDA

View Full Version : Excel and ADO Run-Time Error 3704



DRJD
11-24-2008, 11:49 AM
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

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
Edit: Out of interest, here is the type of stored procedure that the sub is calling...



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

Kenneth Hobs
11-24-2008, 02:58 PM
To iterate the recordset, see what I did here.
http://www.vbaexpress.com/forum/showthread.php?p=167033

DRJD
11-25-2008, 02:24 AM
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 rst.Open sSQL, sConn and ths piece rg.CopyFromRecordset rst 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
11-25-2008, 06:29 AM
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.
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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
, "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 OLEDB:Don'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

DRJD
11-28-2008, 08:29 AM
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
12-07-2008, 04:15 PM
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
03-18-2009, 09:03 PM
This resolved the problem I've been having with a couple of stored procs :hifive: