title: Executing a SQL stored procedure with parameters in VBA
body: <p>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.</p>
  
  <pre><code>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: " &amp; Err.Number
    If Err.Number = 0 Then
      MsgBox "Done", vbOKOnly, "All Done."
    End If
    Exit Sub
  errlbl:
     MsgBox "Error #: " &amp; Err.Number &amp; ", Description:  " &amp; Err.Description, vbCritical, "Error in OpenConnection()"
  Exit Sub
  'Resume exitlbl
  End Sub
  </code></pre>
  
  <p>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.</p>
  
  <p>Many thanks to any help in advance.</p>