VB Prodigy Tutorial (continued)--VB Connect an Access database instance

This article welcomes the non-commercial use of reprint, but need to indicate from the "Programming Network" and the corresponding URL link.

Let's give you an instance of a connection to an Access database, in this case, Access 2003. First in the E-disk to establish a folder called VB, then open Access 2003, click file → new, click Empty database in the task pane that appears on the right, and in the dialog box that appears, we enter the name of the new database after the filename item and choose to save the location. Here we name the new database access_db and save it to the folder we just created, the following figure, after clicking the "Create" button, the system generates a new access empty database.

In an empty database, there are no tables created by any user, and then we will use the designer (the following figure) to create a table for the access_db database.

First we create a table named "Wzdz" for access_db, which consists of the following 4 fields:

(1) Number: This is the primary key field automatically added by Access, which we use directly. We can set the following three fields directly, after entering the fields of the Wzdz table, close the Table Designer, Access prompts you to save the table, select Yes, and then name the table "Wzdz". Because you do not have a primary key defined (primary key), Access prompts you to add a primary key to the table, as prompted to set the number as the primary key.

(2) The following three fields are the site name, site address and site description, and three fields have the same properties, as follows:

▲ Data type: text.

▲ Field Size: 50

▲ Validity rule: none.

▲ Required Fields: No

▲ Allow empty string: No

▲ Index: None

After you set the fields above, and then reopen the Wzdz table in Design view (by right-clicking the Wzdz table name in the designer and selecting the Design view command), you should see the following illustration:

With the database and tables, if you want the VB application to access the database, you need to configure the data source. Only after the configuration has completed the data source, the VB application can make the correct connection work with the database, in the VB application, only through the ADO object to carry out the operation of the specific data, if the database has not been configured or the configuration of the data source is wrong, All the work done before this is in vain.

We follow the steps described in the previous chapter for the configuration of ODBC data sources, set up a user DSN named access_db, then we use VB to specifically connect to the ACCESS_DB database.

First open VB, a new Standard EXE project, in the project Form1 we enter the following code:

Private Sub Form_Load ()

  Dim CNN as ADODB. Connection
  Dim My_recordset as ADODB. Recordset
  Dim connect_string As String
  Dim statestring As String
  Set cnn=new ADODB. Connection
  Set my_recordset=new ADODB. Recordset
  ' Connect Access database
  connect_string= ' dsn=access_db; uid=; Psw= "
  CNN. Open connect_string
  Select case CNN. State case
    Adstateclose
         statestring= "adstateclosed" Case
    adstateopen
         statestring= " adStateOpen "End
  Select"
  shows the status of the connection
  MsgBox "Connection Successful! ",, statestring
  ' wzdz table query operation
  My_recordset. Open "SELECT * from Wzdz", CNN
  My_recordset. Close End

Sub

The code for this connection to the database is written in the Form_Load () child of the form, and the connection to the database is performed at the beginning of the program, which is the form loading process. In Form_Load (), you first define a connection object, CNN, and a Recordset object My_recordset, as well as two variable connect_string and statestring of string types, The next two set statements are designed to generate a connection object, CNN, and a Recordset object My_recordset. Then connect the Access database, first the connection string "dsn=access_db;" uid=; psw= to the string variable connect_string. The specific contents of the connection string are based on the configuration of the ODBC data source, and different configuration methods and configuration content will have different connection strings. CNN's Open event is used to perform connection work with the database. To verify that the connection to the database is correct, we read the State property of CNN here. The State property can be used to indicate that its object status is turned on or off. After the state property is used, the connection status with the database is displayed to the user through the system function MsgBox. If the connection status is open, the text of the dialog box title bar is "adStateOpen", as shown in the following figure:

If the connection status is off, the dialog box title bar displays "Adstateclose".

The following statement my_recordset. Open "Select * from Wzdz", CNN demonstrates how to query the database for the data you need.

Note: Whenever you use a SET statement to generate a connection or Recordset object in your program, you should use the Close method to turn it off when the last use is completed.

Problems that may occur in routine debugging:

The code above may display "user-defined type undefined", incorrectly positioned in Dim CNN as ADODB. Connection "this sentence.

Workaround: Click "Engineering" → "reference" and check "Microsoft ActiveX Data Object 2.6" To run the program without this problem.

See a full set of "VB Prodigy Tutorials" (continued)