How does VB connect to the ACCESS database?

Because VB does not contain classes that can access the database, we need to reference a class that can access the database to use the database. here we use ADODB, which is more flexible and powerful than DAO and APIs that can access the database. Compared with APIs, ADODB is easier to use and more suitable for beginners. the Access database is much simpler than SQL, and can meet the needs of small and medium applications. Therefore, when using the database, we chose Access.

Just like using text files to store data, we need to design the data structure first, but when designing the Access database structure, we need to use other programs for detailed planning. the recommended program is Access in office or VISDATA in VB.

After the database is designed, we can start database programming.

First, we need to reference ADO. the specific method is to find Microsoft ActiveX Data Object * in project -- reference *. * Library, here *. * Indicates the version number of the time ADO. Generally, the application or ActiveX Control has downward compatibility, so we try to select a new version. to ensure that the program can recognize earlier versions of Access, while also identifying newer versions of Access.

Then we need to create an object in the program. just like adding a FileBox on the form to view the file name, we can access the database only when the ADO object is created. there are two common objects: Connection and Recordset.

　　The specific methods for creating these two objects are as follows:

　　1. Use the New Keyword after referencing, as shown in figure

Private Conn As New ADODB. Connection

Private Reco As New ADODB. Recordset

　　2. Use CreateObject to create an object without reference:

Dim Conn, Reco

Set Conn = CreateObject (ADODB. Connection)

Set Reco = CreateObject (ADODB. Recordset)

After the object is created, what we need to do next is to open the database.

Let's take a look at the following code to successfully open the database.

Conn. open Provider = Microsoft. Jet. OLEDB.4.0; Data Source = D: Main. mdb

This code opens the Main. mdb database in drive D.

Connection. the first parameter of the Open method is the connection code, which will be passed to the database engine of the system. provider = Microsoft. jet. OLEDB.4.0, which indicates the database type. different databases may be different. source = d: main. mdb indicates the absolute path of the database.

After you open the database, you also need to open the table. If there is a table in the database named Users, and there are two fields, one is the user name and the other is the password, you can see the following code.

1. If you want to return Users, the [user name] is the [Password] of last year's fireworks.

Recordset. open Select password From Users Where username = 'fireworks last year', Connection

Then we can compare the passwords entered by users to see If logon is allowed.

If Recordset. eof and Recordset. bof then

Msgbox user does not exist !, 16

Else

If PassWord = Recordset (PassWord). value then

Msgbox logon successful !, 64

Else

Msgbox Password error !, 32

End If

End If

Recordset. Close

2. If Admin has successfully logged on to the system, we want to display all usernames and passwords.

Recordset. open Select * From Users, Connection, 1, 1

When the table is opened, we use the following code to display it.

Do whlie Not Recordset. eof

Print User name: & Recordset (User Name). value & password: & Recordset (password). value

Recordset. MoveNext

Loop

Recordset. Close

The preceding code example shows that when you open a table, you can open only one of the fields or all of them. The first parameter is an SQL statement.

Select [field name] From table name [Where condition]

The condition can be omitted here. And the field name can also be replaced.

Note that if you use the method in (1), the code displayed after (2) cannot be used in (1. because the [user name] field is not opened in (1), there is no value for this Recordset (password) and an error may occur.

The following conditions can be operators such as =,>, and <. For example, Where ID> 32. (assume that [ID] is a numeric type .)

This is the opening part. The second important part is querying records.

The database does not store all records in one variable for backup. returns a value in the form of the current record. to find useful information, you must locate and filter the information.

　　Positioning:

Move to the next Recordset. MoveNext

Move to the previous Recordset. MovePrevious

Move to the last Recordset. MoveLast

Move to the first Recordset. MoveFrist

Move to a Recordset. Move Number

　　Filter:

Recordset. Find Condition

For example, [Use method (2) to open a table]

Private Sub commandementclick ()

Recordset. Find username = & text1.text

If Recordset. Eof <> True Then

Msgbox: & Recordset (password). value, 64

Else

Msgbox does not find the user's information !, 16

End If

End Sub

MoveNext is available only when Eof is not True. Otherwise, an error occurs. When MovePrevious is set to Bof, it is not True ....

If one of Eof and Bof is not true, it can be used as long as one record exists.

The conditions in the Find method are exactly the same as those in the first parameter of Open. if this record is not found in the enabled record set, the Eof value is True. if it is found, the current value is a qualified record.

The third part is to add/modify records.

It is easy to modify the record. After finding the relevant record in the above method, assign a value to the record.

For example: [(Change Password) after opening the table by method (1)]

Recordset (password). value = 123456

Recordset. Updata

Note that you must call the Updata method after the modification is completed so that the modification will take effect.

You can use the following code to add a record:

Recordset. addnew

Recordset (User Name). value = Admin

Recordset (password). value = Admin

Recordset. Updata

Here, you must first call the Addnew method, add a new record, assign values to each field in the new record, and then call the Updata method.

It's almost the same here. Finally, let's talk about the methods mentioned above.

Recordset. Open SQL statement, data source, cursor type, Open method

Needless to say, SQL statements are what Select means. The purpose is to return data from the table as required.

The data source is an opened Connection object.

Enter 1.

The open method corresponds to several constants, which can be viewed in the Object Browser.

　　Meanings of corresponding values:

1 read-only 2 exclusive 3 Write 4 Self write, others read.

Connection. open Connection code, server user name, and password.

The connection code here is no longer mentioned. The server user name and password are only used to connect to the remote database.