Stored Procedure in access-parameter query

The access MDB/MDE has a concept similar to the stored procedure (you can also use the create procedure statement of the DDL statement when creating the access), but it is called a parameter query, in addition, a parameter query only supports one jet SQL statement. Therefore, no program flow control statement exists in Jet SQL, and all program flow control statements are controlled by VBA. That is to say, the IF and case statements in the T-SQL do not exist in Jet SQL, but some functions can be replaced by the IIF function and switch function. For details, see access help. Variables can also be defined in Jet SQL, but they are different from those in the T-SQL, so they are called parameters in Jet SQL ".

In fact, the so-called "Stored Procedure" in access (2000 and later versions) is incomparable with the stored procedure in SQL Server. It can only be regarded as "Stored Procedure Lite", does not support multiple SQL statements, does not support logical statements (huh, after all, not a T-SQL) and so on, I do not know whether it is pre-compiled. However, just as the so-called "class" implemented by VBScript is only encapsulation, it can greatly promote the "beautification" of the code structure and the reusability of the program. The "lightweight stored procedure" of access ", for specifications, database operations with a low chance of errors should also be helpful, and the performance may be improved.

 

1. Construct a parameter query in access

Keyword parameters: buildable Parameters

Parameters AA short, BB short; Select Table 1.id From table 1 Where (Table 1.id)> [AA] and (Table 1.id) <= [BB]);

When the preceding parameter query is enabled, the user is prompted to enter the two parameters [AA] [BB. In access, a parameter query directly uses a Form Control to pass parameters to the query. The Code is as follows:

Select Table 1.id From table 1 Where (Table 1.id)> forms! Form! Control A and (Table 1.id) <= forms! Form! Control B ));

When "form a" is opened, you do not need to enter parameters when double-clicking this query. The query will automatically call the values of controls A and B on the form as parameters.

 

2. Generate and call parameter queries in VBA

The parameter query code is as follows:

Parameters stra text; Insert into Table 1 (HH) Values ([stra])

Query the call parameters of VBA or VB as follows:

Public Function appendx () Dim cmdbyroyalty as ADODB. Command Dim prmbyroyalty as ADODB. Parameter Dim rstbyroyalty as ADODB. recordset Dim introyalty as string Set cmdbyroyalty = new ADODB. Command Cmdbyroyalty. commandtext = "query 1" Cmdbyroyalty. commandtype = adw.storedproc Introyalty = trim (inputbox ("input parameter :")) Set prmbyroyalty = cmdbyroyalty. createparameter ("stra", adchar, adparaminput, 255) Cmdbyroyalty. Parameters. append prmbyroyalty Prmbyroyalty. value = introyalty Set cmdbyroyalty. activeconnection = currentproject. Connection Set rstbyroyalty = cmdbyroyalty. Execute End Function

 

Iii. When to use parameter query

The main purpose of using the access stored procedure is to use the extra query provided by parameters. Using the stored procedure, we do not have to worry about the various troubles encountered when splicing the parameter values into SQL statement strings, for example:

Dim SQL SQL = "select * from users where username = '" & username &"'"

In the preceding Code, if the string username contains single quotes ('), an error is returned. We must manually convert:

Dim SQL SQL = "select * from users where username = '" & replace (username ,"'","''")&"'"

Convert to two consecutive single quotes. When using a query with parameters, our SQL statement can be written

Dim SQL SQL = "select * from users where username = @ username"

Then, pass the value of @ username in the parameter attribute of the command object, which is convenient and intuitive.

With cmd 'Create a parameter object . Parameters. append. createparameter ("@ username ") 'Specify values for each parameter . Parameters ("@ username") = Username End

 

4. Use of parameters in the access stored procedure.

Unlike the SQL Server Stored Procedure, the @ variable is used to specify parameters, and the input parameter objects with the same name are different. Parameters in access are identified by "order" rather than "name. You do not need to specify the name of the input parameter. You can specify the name of the parameter in the SQL statement as long as the parameter value is entered. Generally, we use the execute method of the command object to directly input an array of parameter values for execution ~

Cmd. Execute, array (username)

Another example is to write an access stored procedure as follows:

Select * from users where username = p_username and booktitle = p_booktitle

You can do this by passing in an array of parameter values, but the order must correspond:

Cmd. Execute, array (username, booktitle)

 

V. Reference Links:

(1) http://access911.net/fixhtm/79FAB21E12DC.htm? Tt =

(2) http://access911.net/fixhtm/72FAB21E15DC.htm? Tt =

(3) http://access911.net/fixhtm/71FAB51E12DC.htm? Tt =

(4) http://www.haishui.net/view.php? Tid = 18 & id = 278