VBA/Excel/Access/Word/Access/Stored Procedure

Call store procedure

   <source lang="vb">

Public Sub CallStoredProcedure()

 Const ConnectionString As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" + _
   "Persist Security Info=False;Initial Catalog=NorthwindCS;Data Source=LAP800;Workstation ID=LAP800;"
 
 Dim Command As Command
 Set Command = New Command
 
 Command.ActiveConnection = ConnectionString
 Command.rumandText = "[Sales by Year]"
 Command.rumandType = CommandTypeEnum.adCmdStoredProc
 
 Dim BeginningDate As ADODB.Parameter
 Dim EndingDate As ADODB.Parameter
 
 Dim StartDate As Date
 StartDate = #1/1/1995#
 
 Dim EndDate As Date
 EndDate = #1/1/2004#
 
 Set BeginningDate = Command.CreateParameter("@Beginning_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , StartDate)
 Set EndingDate = Command.CreateParameter("@Ending_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , EndDate)
 Call Command.Parameters.Append(BeginningDate)
 Call Command.Parameters.Append(EndingDate)
 Dim Recordset As ADODB.Recordset
 Set Recordset = Command.Execute
 Call Sheet1.Range("A1").CopyFromRecordset(Recordset)

End Sub

</source>
   
  

Create a stored procedure

   <source lang="vb">

Sub CreateStoredProc()

   Dim cmd As ADODB.rumand
   Set cmd = New ADODB.rumand
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "CREATE PROCEDURE procClientGet " & _
       "(CID long) " & _
       "AS SELECT ClientID, CompanyName " & _
       "FROM tblClients " & _
       "WHERE ClientID = CID"
   cmd.Execute

End Sub

</source>
   
  

Creating a Stored Procedure that Accepts Parameters

   <source lang="vb">

Sub Create_SpWithParam()

   Dim conn As ADODB.Connection 
   On Error GoTo ErrorHandler 
   Set conn = CurrentProject.Connection 
   conn.Execute "CREATE PROCEDURE procEnterData(@Company TEXT (40), @Tel TEXT (24)) AS " & _ 
       "INSERT INTO employee (CompanyName, Phone) VALUES (@Company, @Tel);" 

ExitHere:

   If Not conn Is Nothing Then 
       If conn.State = adStateOpen Then conn.Close 
   End If 
   Set conn = Nothing 
   Exit Sub 

ErrorHandler:

   If InStr(1, Err.Description, "procEnterData") Then 
       conn.Execute "DROP PROC procEnterData" 
       Resume 
   Else 
       MsgBox Err.Number & ":" & Err.Description 
       Resume ExitHere 
   End If 

End Sub

</source>
   
  

Deleting a Stored Procedure

   <source lang="vb">

Sub Delete_StoredProc()

   Dim conn As ADODB.Connection 
   On Error GoTo ErrorHandler 
   Set conn = CurrentProject.Connection 
   conn.Execute "DROP PROCEDURE procName; " 

ExitHere:

   If Not conn Is Nothing Then 
       If conn.State = adStateOpen Then conn.Close 
   End If 
   Set conn = Nothing 
   Exit Sub 

ErrorHandler:

   If InStr(1, Err.Description, "cannot find") Then 
       Debug.Print "The procedure you want to delete does not exist."
   Else 
       Debug.Print Err.Number & ":" & Err.Description 
   End If 
   Resume ExitHere 

End Sub

</source>
   
  

Executing a Parameterized Stored Procedure

   <source lang="vb">

Sub Execute_StoredProcWithParam()

   Dim conn As ADODB.Connection 
   Dim strCompany As String 
   Dim strPhone As String 
   On Error GoTo ErrorHandler 
   Set conn = CurrentProject.Connection 
   strCompany = "nnn"
   strPhone = "123123"
   If strCompany <> "" And strPhone <> "" Then 
       conn.Execute "procName " & strCompany & ", " & strPhone 
   End If 

ExitHere:

   If Not conn Is Nothing Then 
       If conn.State = adStateOpen Then conn.Close 
   End If 
   Set conn = Nothing 
   Exit Sub 

ErrorHandler:

   Debug.Print Err.Number & ":" & Err.Description 
   Resume ExitHere 

End Sub

</source>
   
  

Executing a Stored Procedure Containing Parameters

   <source lang="vb">

Public Sub UpdateWithStoredProcedure()

       Dim cmd As New ADODB.rumand
       Dim conn As ADODB.Connection
       Dim prm As ADODB.Parameter
       Dim strConn As String
       Dim strSQL As String
       strConn = "Provider=SQLOLEDB.1;" & _
           "Data Source=(local); Initial Catalog=NorthWind;" & _
           "Integrated Security=SSPI"
       Set conn = New ADODB.Connection
       conn.Open strConn
       Set cmd = New ADODB.rumand
       cmd.rumandText = "procOrderUpdate"
       cmd.rumandType = adCmdStoredProc
       cmd.ActiveConnection = conn
       Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
       cmd.Parameters.Append prm
       cmd.Parameters("OrderID").Value = 1
       Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
       cmd.Parameters.Append prm
       cmd.Parameters("OrderDate").Value = "1/1/2007"
       Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
       cmd.Parameters.Append prm
       cmd.Parameters("ShipVia").Value = 2
       Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
       cmd.Parameters.Append prm
       cmd.Parameters("Freight").Value = "10.5"
       "Execute the Stored Procedure
       cmd.Execute
       "Close the connection
       conn.Close
   End Sub
</source>
   
  

Use the EXECUTE statement to execute the stored procedure

   <source lang="vb">

Sub CreateStoredProc()

   Dim cmd As ADODB.rumand
   Set cmd = New ADODB.rumand
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "CREATE PROCEDURE procClientGet " & _
       "(CID long) " & _
       "AS SELECT ClientID, CompanyName " & _
       "FROM tblClients " & _
       "WHERE ClientID = CID"
   cmd.Execute

End Sub Sub ExecuteStoredProc()

   Dim rst As ADODB.Recordset
   Dim cmd As Command
   Set cmd = New ADODB.rumand
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "EXECUTE procClientGet 1"
   Set rst = cmd.Execute
   MsgBox rst("CompanyName")

End Sub

</source>