HandsOn Executing a Parameterized Stored Procedure

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the module's Code window, enter the Execute_StoredProcWithParam procedure as shown below.

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 = InputBox("Please enter company name;", "Input Company")

strPhone = InputBox("Please enter the phone number:", "Input Phone")

If strCompany <> "" And strPhone <> "" Then conn.Execute "procEnterData " & 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:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

When you run the parameterized stored procedure in Hands-On 23-5, Access displays an input box for each parameter where you can type in the value you want to insert. After you have supplied both required parameters, a new record is entered into the Shippers table.

Another way to write a statement to execute a stored procedure is by using the SQL EXECUTE command (or its shortened version — EXEC) like this:

conn.Execute "EXECUTE procEnterData " & strCompany & ", " & strPhone or conn.Execute "EXEC procEnterData " & strCompany & ", " & strPhone

Part III

0 0

Post a comment