[AccessD] Execute stored procedures from Access

Here's some antique code that might do what you want.  It was created
way back in the 2000 days, so who knows if it will even run now.  No
guarantees, YMMV.

Charlotte

Public Function CallADOStoredProc(ByVal SPName As String, _
                                  ParamArray Params() As Variant)
  'Created by Charlotte Foust 9/30/2000
  'last modified 1/12/2001
  'Calls a saved query or stored procedure
  On Error GoTo Proc_err
  Dim varValue As Variant
  Dim strSQL As String
  Dim intLoop As Integer
  Dim varPrmType As Variant
  Dim lngRecords As Long
  
  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim errCurr As ADODB.Error
  Dim colErrs As ADODB.Errors
  
  Const ERR_OPER_ON_INVALID_CONNECTION = 3709
  Const ERR_RECORD_IS_DELETED = -2147467259
  
  'this simplifies accessing the errors
  'collection, which belongs to the
  'connection
  Set cnn = CurrentProject.Connection
  Set colErrs = cnn.Errors
  
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cnn
    'this could also be written as
    'colErrs.Clear
    .ActiveConnection.Errors.Clear
    .CommandType = adCmdStoredProc
    .CommandText = SPName
    
    For intLoop = LBound(Params) To UBound(Params)
      Select Case varType(Params(intLoop))
        Case vbString
          varPrmType = adVarWChar
        Case vbLong
          varPrmType = adBigInt
        Case vbDate
          varPrmType = adDate
          'if SQL Server, use adDBTimeStamp
        Case vbInteger
          varPrmType = adSmallInt
        Case vbDouble
          varPrmType = adDouble
        Case vbSingle
          varPrmType = adSingle
        Case vbBoolean
          varPrmType = adBoolean
        Case vbCurrency
          varPrmType = adCurrency
           Case vbByte
          varPrmType = adUnsignedTinyInt
        Case vbNull
          varPrmType = Null
        Case Else
          'WARNING!  Not supported in ADO 2.5
          varPrmType = adVariant
      End Select
      If varPrmType = adVarWChar Then
        .Parameters.Append .CreateParameter( _
              "prm" & intLoop, varPrmType, adParamInput,
Len(Params(intLoop)) + 2, Params(intLoop))
      Else
        'you have to create ALL the parameters
        .Parameters.Append .CreateParameter( _
              "prm" & intLoop, varPrmType, adParamInput, ,
Params(intLoop))
      End If 'varPrmType = adVarWChar
    Next intLoop
    .Execute RecordsAffected:=lngRecords, Options:=adCmdStoredProc
  End With
Proc_exit:
  On Error Resume Next
  CallADOStoredProc = lngRecords
  Set cmd = Nothing
  Exit Function
Proc_err:
  'ADO errors and Jet errors aren't the same
  'collection, so this handles them differently
  If colErrs.Count > 0 Then
    'There are ADO errors
    For Each errCurr In colErrs
      Select Case errCurr
        Case ERR_OPER_ON_INVALID_CONNECTION
        Stop
          Resume Proc_exit
        Case Else
            MsgBox errCurr.Number & "--" _
                    & errCurr.Description & " (" _
                    & errCurr.Source & ")"
            Resume Proc_exit
      End Select
    Next errCurr
    colErrs.Clear
  Else
    'there is an other error
    MsgBox Err.Number & "--" & Err.Description
    Resume Proc_exit
  End If
End Function 'CallADOStoredProc(ByVal SPName As String, _
                                  ParamArray Params() As Variant)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 21, 2009 8:47 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Execute stored procedures from Access

Does anyone have commented code to execute SQL Server stored procedures
with parameters from Access code?

--
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com