Accessing an Access database in an ASP program

access| Program | access | data | Database in the Microsoft IIS/PWS Network platform, through the server-side running ASP program to access the background database, is one of the most common mode. For small database application requirements, Microsoft's Access database should be the first choice for the use of ASP programs. Because the ODBC driver for an Access database supports a full SQL directive that is highly efficient, the access background database +asp server-side program + client IE browser is a streamlined, practical and efficient combination mode.

In this mode of use, the ASP program is undoubtedly the most important, is the communication between the client and the backend database bridge. In the ASP program, through the VB Script, establishes the connection to the Access database, is the premise which the customer can access the backstage database.

A common way to establish Access database connections

The general format for establishing an Access database connection in ASP is as follows:

<%

Dbpath=server.mappath (database name)

Set conn=server.creatobject ("ADODB. Connection ")

Conn.Open "Driver={microsoft Access driver (*.mdb)};d bq=" & DBPath

Set rs=server.creatobject ("ADODB. Recordset ")

Rs.Open data table name or SQL instruction, connection object, Recordset type, lock type

... ...

%> its various steps and parameters are as follows:

First line program: Take advantage of the MapPath function of the server object, get the complete file path to open the database, and store it in the variable dbpath. In which, the database name is the parameter we need to specify, and should be replaced with the actual name of the database we want to open. If the database name appears directly as a constant, enclose it in quotation marks, and you cannot discard the extension. For example, the database is Test.mdb, then the line program becomes: Dbpath=server.mappath ("Test.mdb").

Second-line program: Create a Connection object in an ADO object set, or join the object. This is the initial step in establishing a database connection. After executing this line of procedures, Conn becomes a connection object.

Third-line program: Open a specified database with the open method of the Connection object Conn. Because we want to open an Access database, you specify the ODBC driver parameters to access the database through the ODBC driver for access: Driver={microsoft Access Driver (*.mdb)};. Another parameter, dbq= & DBPath, is equivalent to Dbq=server.mappath (database name) and uses the Server.MapPath (database name) function of the first row to specify the database file to open. Here, you have opened the database specified by the database name. If the database name is "Test.mdb", open the Access database Test.mdb. The parameters specified in this line must be written in exactly the same format, not omitted or altered, and there are no variable parameters.

Line four: Create a Recordset object in an ADO object set to manipulate the database using the Recordset object (which is, of course, just one of several ways to manipulate the database). When this line is executed, RS becomes a Recordset object.

Five-line program: Open the data table in the database with the Open method of the RS object. This has four parameters, and the meaning is as follows

Datasheet name or SQL instruction string: In this parameter, specify the name of the datasheet in the database to open, or the specified range data for the datasheet that is determined by the SQL Select instruction string, for example, if there is a data table number in the database Test.mdb, the parameter becomes "number", Note that the quotation marks cannot be dropped, and if you want to open data records in datasheet number that are less than 90 in the XH field, the parameter may become the following form:

"SELECT * from number Where XH < 90".

Connection object: Specifies the connection object of the database that is already open, where the fix is conn and note that there is no quotation mark.

Recordset Type: Represents the way to open a datasheet, with four choices. The number 0 represents read-only mode, and the current record can only be moved down, the number 1 is read-write, the current record is free to move up and down, but you can't see the new records created by another user in time, unless you restart it, the number 2 means read-write, the current record is free to move, and you can see new records ; Number 3 means read-only, but the current record is free to move. The general choice of 2 is good unless the data is modified in order to prevent it.

Lock type: This parameter specifies the locking function of the database. Because databases on the network are multi-user, it is likely that multiple users are working on the database at the same time. In order to avoid errors, it is only possible to have one user modify the data at the same time using the lock function. There are four options: The number 1 is read-only lock, the user cannot change the data, and the number 2 represents pessimistic locking, and when a user starts modifying the data with an RS object, it locks the database until the user uses Rs. Update the record to unlock the lock, the number 3 for optimistic locking, only in the data written to the database when the lock, not insurance, use caution! The number 4 indicates a batch optimistic lock, only using Rs. UpdateBatch data records are locked when data is updated in batches. Belonging to a rarely used. Generally, using pessimistic locking is safer, but less efficient.

[1] [2] Next page