I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute
Here is a code snip, any Ideas? TIA Mike
Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset
Con1.Open
Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1
Worksheets("T1").Range("B1").CopyFromRecordset Rs
I believe it should be like
Con1.Open
Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute
VMazur,
Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType
Any other thoughts would be appreciated.
'Open the connection
Con1.Open
Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute
'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open
MikeD
|||What happens when you call Execute method? Does it generate any error or not?|||The issue was Set NO COUNT ON in the SP. This was missing in the SP.
As soon as I added this, the recordset opened and I could do copy from.
Thank you Mazur
|||Hi Mazur,
There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.
This question is resolved.
Thank you
Mike
