Macro -> SQL Stored Procedure -> CopyFromRecordSet err

lenny3k

New Member
Joined
Jan 15, 2003
Messages
18
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
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
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
 

Forum statistics

Threads
1,078,013
Messages
5,337,728
Members
399,167
Latest member
Bellzebub

Some videos you may like

This Week's Hot Topics

Top