[AccessD] Execute stored procedures from Access

John,

Here is what I use (this is for an Access XP adp).  Not sure what 
comments you require:

The following function is used to get an ADODB Command:

Public Function basGetCommand(strCommandText As String, commandType 
As ADODB.CommandTypeEnum) As ADODB.Command

      On Error GoTo Exit_basGetCommand

      Dim adocmd As ADODB.Command

      Set adocmd = New ADODB.Command
      With adocmd
         .ActiveConnection = CurrentProject.Connection
         .commandText = strCommandText
         .commandType = commandType
         .CommandTimeout = 0
      End With

      Set basGetCommand = adocmd
      Set adocmd = Nothing

Exit_basGetCommand:
      Exit Function

Err_basGetCommand:
      Select Case Err
          Case 0:
          Case Else
              Call basErrorMsg("basGetCommand - " & strCommandText)
      End Select
      Resume Exit_basGetCommand
      Exit Function

End Function

To run a stored procedure I use something like this (this stored 
procedure inserts records into a table):

     Dim cmd As ADODB.Command

     Set cmd = basGetCommand("dbo.sprptTeamSaleSummary", adCmdStoredProc)
     cmd.Parameters.Append cmd.CreateParameter("@MinSales", 
adInteger, adParamInput, , Me!txtSaleMinimum)
     cmd.Execute
     Set cmd = Nothing


To get a recordset from a stored procedure I use something like this:

     Dim rst As ADODB.Recordset, cmd As ADODB.Command

     Set cmd = basGetCommand("dbo.spsrpProspectSalesStats", adCmdStoredProc)
     cmd.Parameters.Append cmd.CreateParameter("@FirstDate", adDate, 
adParamInput, , basConvertDate(Me!txtSaleFirstDate))
     cmd.Parameters.Append cmd.CreateParameter("@LastDate", adDate, 
adParamInput, , basConvertDate(Me!txtSaleLastDate))
     Set rst = cmd.Execute()
     rst.MoveFirst
     Do Until rst.EOF
...etc


Public Function basConvertDate(dteDate As Date) As String
'Used to convert date to SQL string format

     basConvertDate = CStr(DatePart("yyyy", dteDate)) & "-" & 
CStr(DatePart("m", dteDate)) & "-" & CStr(DatePart("d", dteDate))

End Function


Hope this helps.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand

At 22/01/2009, you wrote:
>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