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

Call store procedure

 
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

Create a stored procedure

 
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

Creating a Stored Procedure that Accepts Parameters

 
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

Deleting a Stored Procedure

 
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

Executing a Parameterized Stored Procedure

 
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

Executing a Stored Procedure Containing Parameters

 
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

Use the EXECUTE statement to execute the stored procedure

 
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