Executing a Parameterized Stored Procedure

In the preceding section, you learned how to run a parameterized stored procedure from the Access user interface. To execute an existing stored procedure from VBA code, you can use the Execute method of the ADO Connection or Command object. Here's how:

■ With the Execute method of the Connection object: conn.Execute "procName"

■ With the Execute method of the Command object:

cmd.CommandText = "procName" cmd.CommandType = adCmdStoredProc cmd.Execute rst. Open cmd

If the stored procedure requires parameters, parameter values follow the procedure name as a comma-separated list. Here's an example procedure that executes the procEnterData stored procedure and contains the values for its two parameters:

Sub RunProc_WithParam()

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection conn.Execute "procEnterData ""My Company2"", ""(234) 334-3344

conn.Close Set conn = Nothing End Sub

Instead of surrounding parameters with sets of double quotes, you can use single quotes like this:

conn.Execute "procEnterData 'My Company2', '(234) 334-3344'"

Programming with the Jet Data Definition Language

The procedure in Hands-On 23-5 runs the stored procedure named procEnterData created in Hands-On 23-4. Notice how this procedure uses the InputBox function to obtain the parameter values from the user instead of hard-coding them in the Execute method of the Connection object (as shown above). Still another way of providing parameter values to a stored procedure would be via an Access form. I will leave this idea for you to try on your own.

0 0

Post a comment