Creating a Stored Procedure that Accepts Parameters : Stored Procedure « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Stored Procedure

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 

 

Related examples in the same category