Working with & passing recordsets 1

[sdk]

Hi,

I am developing an excel(2k) spreadsheet that retrieves data from an Oracle database, and then loads that data into the spreadsheet.

The structure I am after is:

public sub retreiveManager()
...
set rst = getOracleData()
loadSpreadSheet rst
...
end sub

public Function getOracleData() as Recordset
...
Set getOracleData = localRst
Set localRst = Nothing
...
end Function

public sub loadSpreadsheet(rst as RecordSet)
...
with rst
'My trouble appears here
while not .EOF
(do things)
.MoveNext
loop
end with
...
end sub

the trouble occurs when I try to refer to recordset in the functions away from where the recordset is created, in particular loadSpreadSheet(). I can play with the results in the getOracleData function (so it works fine) and use loadSpreadSheet directly from that function, but not elsewhere. When trying to reference the recordset object in the other functions, the error 'Object not valid or no longer set' is trapped. I understand the error itself, but not how to resolve it.

Any help appreciated.

Cheers & thanks

Steve

 

[krinid]

I believe your problem lies in that you close the DB before you've taken the data from the recordset. The recordset is just pointing to the actual live DB results, and when the DB is closed, the recordset is no longer accessible, and will produce the same error as a when you Dim an object but don't Set it to a valid object.

Here's a quick way to get data from a recordset into an Excel sheet "as-is":

numRecords = WS.Cells(1, 1).CopyFromRecordset(RS)

NOTE:
- WS = WorkSheet you want to copy data to
- numRecords is return value = # of records in recordset
- Cells(1,1) is the location you want to paste to

 

[sdk]

Thanks for that. I realised the recordset was closing, but couldn't workout why - your info v.helpful. Will give your codelet a go - looks far more elegant than my work!

Cheers & thanks

Steve