Stored Procedure in access-parameter query

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.