Executing a SQL stored procedure with parameters in VBA

Question

I'm fairly new to this. I'm trying to write a procedure in VBA that executes a stored procedure from excel and returns the record set. It currently returns the results of the stored procedure in spreadsheet 2 but I've had to hard code the parameter into the SQL code. Here is what I have pinched from other online forums.

Option Explicit

Public Sub OpenConnection()

'Set the variables
Dim conn As ADODB.Connection
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim fld
Dim i As Integer

'Error handler
On Error GoTo errlbl

'Open database connection
Set conn = New ADODB.Connection

'First, construct the connection string.

'ODBC CONNECTION YOU'VE ALREADY SET UP:
conn.ConnectionString = "DSN=PC_Tool_Coding"

conn.Open       'Here's where the connection is opened.

Debug.Print conn.ConnectionString  'This can be very handy to help debug!

'Recordset
Set rs = New ADODB.Recordset

str = "exec Select_account_info"

'recordset is opened here
rs.Open str, conn, adOpenStatic, adLockReadOnly

If Not IsEmptyRecordset(rs) Then
    rs.MoveFirst

    'Populate the first row of the sheet with recordset’s field names
    i = 0
    For Each fld In rs.Fields
        Sheet2.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
        i = i + 1
    Next fld
    'Populate the sheet with the data from the recordset
    Sheet2.Range("A2").CopyFromRecordset rs


Else
    MsgBox "Unable to open recordset, or unable to connect to database.", _
       vbCritical, "Can't get requested records"

End If

'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

exitlbl:
  Debug.Print "Error: " & Err.Number
  If Err.Number = 0 Then
    MsgBox "Done", vbOKOnly, "All Done."
  End If
  Exit Sub
errlbl:
   MsgBox "Error #: " & Err.Number & ", Description:  " & Err.Description, vbCritical, "Error in OpenConnection()"
Exit Sub
'Resume exitlbl
End Sub

I've looked around on how to get it to work with parameters and I just can't seem to get there. The parameter I'll be using is called @accgrpnum in SQL. Its a 12 letter string.

Many thanks to any help in advance.

What database vendor are you using? (i.e. MS SQL, MySQL, DB2, etc.) — hall.stephenk, Nov 29 '12 at 17:26
Have you taken a look at [run a parametrized SQL stores procedure in vba](http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba) and all similar questions on your right hand side? On mobile, else would have given your a code a boost. — bonCodigo, Nov 29 '12 at 17:30
Hall.stephenk : it looks more like connecting to access or excel itself on a temp DB style — bonCodigo, Nov 29 '12 at 17:32

score 1 · Answer 1 · answered Feb 08 '13 at 21:42

1

Take a look at bonCodigo's link or

I've also been able to do this (with SQL Server 2008) by sending more than one statement, but separated in a SQL statement like

str = "SET @accgrpnum = 'my_account'; exec Select_account_info;"

answered Feb 08 '13 at 21:42

Jacob Young

374
3
13

Executing a SQL stored procedure with parameters in VBA

1 Answers1