Use "Stored Procedure" in access [Post]

Source: Internet
Author: User
Tags mdb database

From: Id = 499
We are already familiar with using ASP to call SQL Server Stored Procedures to perform database operations. But do you know that in table-level database access, we can also create and use stored procedures "?

Access + ASP is an excellent combination of lightweight Web applications: simple, fast, and compatible, but the performance is usually not high. In addition, ADODB is used. connection and recordset objects are also inconvenient to execute SQL statements, because the parameter values of SQL statements with parameters are often spliced into strings, as a result, there is such a problem as "single quotes. One advantage of using stored procedures is that it supports SQL statement parameter values.

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.
Next I will translate the step by step method to introduce how to create a stored procedure in access and then use it in ASP programs.

(1) create a "Stored Procedure" in access"

I don't know what the access application level is. For me, it is just a tool for creating mdb database files. I will only create MDB files, create tables, indexes, and constraints on the access interface ~
In access, "query" assumes the role of a stored procedure. The "Stored Procedure" or "query" of access mentioned below refer to this item.
For the creation of "query", Access provides a dumb tool, similar to the wizard used to create a dataadapter in vs. net. However, I like to write SQL code directly.
Well, let's take a look at the table structure of the database used in this simple example.

Click "query" on the left of the access interface, and then double-click "create query in design view" on the right to open the query design view.

In this case, a visual query generator is displayed. We first add the tables to be involved in the SQL statement.

After adding a table, right-click the design view and select "SQL View" to switch to the SQL code editing window.

Okay. Let's talk about the features of the access stored procedure.

Access query, I feel like it is a packaging of SQL statements, maybe some optimization, such as pre-compilation. We cannot use multiple operations, transactions, logical judgment, loops, etc. like writing SQL server stored procedures ......
However, 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 face any troubles when splicing the parameter values into SQL statement strings, for example:

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:

SQL = "select * from users where username = '" & replace (username, "'", "'' ") &" '"' is converted to two consecutive single quotes
When using a query with parameters, our SQL statement can be written as follows:

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

The usage of parameters in the access stored procedure is also described here. 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)

OK. Let's take a look at the two queries used in our example. One is to write data. Write the SQL statement, save it, and name it.

Another Stored Procedure Code for reading data.

(2) Use stored procedures
Then we can call these stored procedures in the ASP program.
Here we can see why the query in access is its stored procedure -- The commandtype attribute of our command object is set to 4, that is, stored proc! So...

Option explicit

Dim s
S = RND * 100

Dim Conn, cmd
Set conn = server. Createobject ("ADODB. Connection ")
Set cmd = server. Createobject ("ADODB. Command ")

Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("sp. mdb ")

With cmd
. Activeconnection = Conn
. Commandtype = & h0004' Stored Procedure
. Commandtext = "addnewdata"

Cmd. Execute, array (CSTR (now (), csng (s ))

With cmd
. Activeconnection = Conn
. Commandtype = & h0004' Stored Procedure
. Commandtext = "getdata"

Dim resultrs, resultarray
Set resultrs = cmd. Execute (, null)

If not resultrs. EOF then
Resultarray = resultrs. getrows ()
End if

Set resultrs = nothing
Set cmd = nothing
Conn. Close
Set conn = nothing

Response. Write "<ul>"
Dim I
For I = 0 to ubound (resultarray, 2)
Response. Write "<li>" & resultarray (0, I)
Response. Write "" & resultarray (1, I)
Response. Write "" & resultarray (2, I)
Response. Write "</LI>"
Response. Write "</ul>"

Running result:

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: 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.