Macro -> SQL Stored Procedure -> CopyFromRecordSet err

[lenny3k]

Hi there.

I'm attempting to use a VB Macro to retrieve a recordset from a SQL Server stored procedure which takes in two parameters - and then paste the results into a worksheet.

although I seem to be successful in retrieving the recordset (I can print out the column names OK) - I'm having trouble with the CopyFromRecordSet function ("Method CopyFromRecordSet of Object Range failed").

Any suggestions wuold be appreciated!......

Dim con As Connection
Dim wsp As Workspace
Dim rst As Recordset
Dim qry As QueryDef
Dim ReportQuery As String


Set wsp = CreateWorkspace("", "admin", "", dbUseODBC)
Set con = wsp.OpenConnection("", dbDriverNoPrompt, True, "ODBC;DATABASE=mydb;UID=myusr;PWD=mypwd;DSN=MyServer")

ReportQuery = "{call p_MyStoredProc (?,?)}"

Set qry = con.CreateQueryDef("RunStoredProc", ReportQuery)
qry.Parameters(0).Type = dbText
qry.Parameters(0) = 'TestParam'
qry.Parameters(1).Type = dbInteger
qry.Parameters(1) = 1


For nCols = 0 To rst.Fields.Count - 1
Worksheets("worksheet1").Cells(1, nCols + 1).Value = rst.Fields(nCols).Name
If nCols <> 0 Then
Worksheets("worksheet1").Columns(nCols + 1).EntireColumn.AutoFit
End If
Next

'The following causes "Method CopyFromRecordSet of Object Range failed"
Worksheets("worksheet1").Range("A1").CopyFromRecordset rst, rst.RecordCount, rst.Fields.Count

 

[Legacy 98055]

Get rid of your last two optional arguments. There is no need for them if you are copying the entire recordset to the range.

"rst.RecordCount, rst.Fields.Count"

You are using the default cursortype which is probably throwing an error with rst.RecordCount = 0