Error Accessing recordset from stored proc   

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.

Any suggestions anyone?......

Code below:




    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

date: Tue, 22 Nov 2005 12:30:02 -0800   author:   len

Re: Error Accessing recordset from stored proc   

You probably want to copy to A2 rather than A1 to avoid overwriting the 
headings...but that is probably not the problem.

CopyFromRecordset can give problems on some combinations of Excel and 
Windows versions.

If you replace the CopyFromRecordset line with 
  lRow = 2
  Do Until rst.EOF
    For nCols = 0 To rst.Fields.Count - 1
      Worksheets("worksheet1").Cells(lRow, nCols+1) = rst(nCols)
    Next
    rst.MoveNext
    lRow = lRow+1
  Loop

does that work?

Bill Manville
MVP - Microsoft Excel, Oxford, England

date: Wed, 23 Nov 2005 23:45:17 GMT   author:   Bill Manville