Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

RunTime Error 3704 Loading Recordset from Stored Procedure

I am attempting to load a recordset generated by an SQL stored procedure (no parameters) into Excel using VBA. When my code hits the last line, highlighted below, a "RunTime Error 3704: Application-Defined or Object-Defined Error" error occurs.

Any ideas?

Dim RSData2 As ADODB.Recordset
Dim DBConn As New ADODB.Connection

Const stADO = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Initial Catalog=PennTel_Core_Demo;" & _
"Data Source=172.17.9.44"
stSQL = "aaa_WPAHS1"

Set RSData2 = New ADODB.Recordset

'On Error GoTo Cleanup

Call RSData2.Open(stSQL, stADO, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdStoredProc)

Call Range("C10").CopyFromRecordset(RSData2)

kedst1

[912 byte] By [kedst1] at [2007-12-23]
«« Oracle Error on x64 bit
»» Make AreaPath field required.
# 1

Hi

I would normally use the below syntax

Range("C10").CopyFromRecordset RSData2

But I suspect that the recordset has not been correctly returned. Try checking to see if the recordset has records or is equal nothing before this line. I have seen a number of posts where the time taken to execute procedures has caused simalar problems

Regards

ADG

ADG at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2

ADG,

Thanks for the response. What are the best ways to test the state of the recordset? I have tried using the record count property and trying to get a return value on the recordset itself without success. Are other methods of doing this?

Thanks, any help is appreciated.

kedst1

kedst1 at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified