First knowledge of VB Database Development Instance 2 (create record browsing window)

Production features

Use ADO to access the data source-Features

1. Accessing a database through an ODBC Data Source represents how to establish an ODBC data source for a relational database

2. Use the ADO recordset object to save data and execute the update method for real-time update.

3. Display and modify data using text boxes and button events

 

Design Analysis

In fact, you can use ADO to directly access the 2000 database (system logon). This force uses the ODBC data source to access the ODBC data source at the end of the period.

How to update a data source using ADO

1. Save the data retrieval to the recordset object and update it using the update method. If you use a recordset object that is disconnected, update it using the updatebatch method.

2. Use the command object to execute the SQL data update command. This does not need to use the recordset object or use other methods to retrieve data to the local computer.

 

Technical Essentials

The locktype attribute of recordset is used to specify the record lock type.

Adlockreadonly: Default Value, read-only, unable to update data

Adlockpessimistic

Adlockoptimistic: Open record lock, locking record when the update method is executed

Adlockbatchoptimistic: an open batch update, indicating that the recordset uses the updatebatch method to update multiple records, that is, the record is locked when the updatebatch method is executed.

 

The temporary table location of the recordset (the cursorlocation attribute is used to specify the temporary table location) can be set

Aduseclient: temporary table on the client, which provides unique functions such as re-sorting and searching records using indexes.

Aduseserver: temporary table on the server side, maintained by the retrieved data source on the server side

Note: The recordset In ADO is a one-way read-only temporary table on the server by default.

 

Browse records in recordset

Two steps are required to browse records in recordset. First, locate the current record. When recordset is enabled, the first record is the current record. Then, use the field object to obtain the data columns of the current record.

The recordset object can locate the current record using the following five methods:

1. movefirst: point the record pointer to the first record.

2. movenext: Use the record pointer to point to the next record.

3. moveprevious: indicates the forward record of the record pointer.

4. movelast: point the record pointer to the last record.

5. Move N: point the record pointer to the nth record counted from the current record

Note: When you browse recordset, if Bof is true, the Record Pointer Points to the first record. If the EOF attribute is true, the Record Pointer Points to the last record. These two attributes can also be used to test whether recordset is null.

 

ODBC Data Source Type

User DSN: local data source of the computer, which can only be used by the current user.

System DSN: local computer data source, which can be used by any user with Permissions

File DSN: it can be shared by users who have installed the same driver instead of a local data source on the computer.

Note: When establishing a data source connection, the connection object uses the msdasql driver by default. Therefore, you only need to specify the data source name in the connection string. For example:

Objcn. Open "DSN = myaccess" 'create a database connection

 

Procedure

 

1. Create an oodbc Data Source

Choose system start> program> Management Tools> Data Source

Click Add and select Microsoft Access driver.

 

Click Finish to open the installation dialog box.

In the data source name text box, enter "instance 2dsn" as the name of the new data source, enter "access data source for instance 2" in the description text box, and click the select button.

Click OK to close the dialog box

To set administrator options such as logon name and password for the ODBC data source, click Advanced to enable advanced options.

If you use an Access database with a system database, select "ODBC
In the Microsoft Access installation dialog box, click system database.

If you want to set additional options for the driver, including page timeout, buffer size, exclusive zone size, exclusive and read-only options, click

The option button in the "ODBC Microsoft Access installation" dialog box, and the driver sequence additional options are displayed at the bottom of the dialog box.

Click OK to close the installation dialog box. The new instance 2dsn data source is displayed in the "ODBC data source Manager" dialog box.

Click OK to close the "obdc data source Manager" dialog box and complete data source creation.

 

Design Record browsing form

Note: Set the locked attribute of the bottom text box to true.

 

Create ADO reference

Project-> reference select Microsoft ActiveX Data Objects 2.5 Library

 

Write code

Dim objrs as recordsetdim objcn as connectiondim isneedsave as Boolean 'is used to identify whether a save operation is required.'Add the code of the previous record. When you click the button, save the changes to the current record and set the previous record as the current record.Private sub commandateclick () dosave objrs. moveprevious if objrs. bof then objrs. movefirst displays the current record txtusername = objrs. fields ("username "). value txtpassword = objrs. fields ("password "). value txtmsg = objrs. absoluteposition & "/" & objrs. recordcount end sub'Add the code of the last record. When you click the button, save the changes to the current record and set the next record to the current record.Private sub command2_click () dosave objrs. movenext if objrs. EOF then objrs. movelast displays the current record txtusername = objrs. fields ("username "). value txtpassword = objrs. fields ("password "). value txtmsg = objrs. absoluteposition & "/" & objrs. recordcount end sub'Create a data source connection and retrieve the data. The first record retrieved is displayed in the form.Private sub form_load () set objcn = new connection 'instantiate the connection object' to connect to the database objcn. open "DSN = instance 2dsn" set objrs = new recordset 'create a client record set with objrs. cursorlocation = aduseclient 'specifies the location of the temporary table. cursortype = adopendynamic 'specifies the key set cursor. locktype = adlockoptimistic 'specifies the lock type. open "select * from system user", objcn end with 'displays the first record set txtusername = objrs. fields ("username "). value txtpassword = objrs. fields ("password "). value txtmsg = objrs. absoluteposition & "/" & objrs. recordcount end sub'Define a process to update dataPrivate sub dosave () If isneedsave then objrs. fields ("username "). value = txtusername objrs. fields ("password "). value = txtpassword objrs. update 'execute the update operation end ifend sub'. If data is modified, set isneedsave to trueprivate sub txtpassword_change () isneedsave = true'. When modifying the field of the current record, set the Save ID end subprivate sub txtusername_change () isneedsave = true' when modifying the current record field, set the Save ID end sub 'unload Event code, when the form is closed, release the database connection and the ADO object private sub from_unload (cancel as integer) set objrs = nothing 'to release the record set object objcn. close 'release database connection set objcn = noth' release connection object end sub