ADO Tutorials - Lesson 5: The ADO Set of Records

	The ADO Set of Records

A Set of Records

Creating a table provides you with a structure in which you can create a list of values. When a table has been created, it is initially empty but an empty placeholder is automatically created for each column:

Family Members

Name	Age	Gender	Relationship

Therefore, after creating a table, you can populate it. Since an empty placeholder is created under each column, you can start by adding one value for each category. When a value has been created for each column, even if you skip some columns, you are said to have created a record. Here is an example:

Family Members

Name	Age	Gender	Relationship
Judie	18	Female	Sister

In the same way, you can keep adding records as you see fit. Every time you have created a record, a placeholder for a new record is automatically made available so you don't have to wonder when a record would go. This also implies that each new record you create is appended, that is, it is added to the end, of the table. Here is an example of a table with records:

Family Members

Name	Age	Gender	Relationship
Judie	18	Female	Sister
Ernest	24	Male
Bill	52		Uncle
David	36	Male	Brother
Hermine		Unknown	Niece

Practical Learning: Introducing Record Sets

If you want to follow this lessons, Start Microsoft Visual Basic 6 or your programming environment
On the opening dialog box, click Standard EXE and click Open
Save the project in a new folder inside of your ADO Lessons folder and name it MVA2
Save the file of the form as Main and save the project as Motor Vehicle Administration1
Double-click an empty area of the form to access its Load event

Use the same approach as done previously to create a new database named MVA and store it in the above Tables folder (don't forget to add a reference to ADOX using the References dialog box):

Private Sub Form_Load()
    Dim conCatalog As ADOX.Catalog
    
    Set conCatalog = New ADOX.Catalog
    conCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\ADO Lessons\MVA2\MVA.mdb'"
    
    MsgBox "A new Microsoft JET database named MVA.mdb has been created"
    
    Set conCatalog = Nothing
End Sub

Execute the application
Click the OK button then close the form

To create a new table, change the code of the Load event event as follows:

Private Sub Form_Load()
    Dim conEmployees As ADODB.Connection
    
    Set conEmployees = New ADODB.Connection
    conEmployees.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\ADO Lessons\MVA2\MVA.mdb'"
    
    conEmployees.Execute "CREATE TABLE Employees(" & _
                         "EmplNumber Text(6)," & _
                         "DateHired DateTime," & _
                         "FirstName VarChar(20)," & _
                         "LastName varchar(20)," & _
                         "Department varchar(40)," & _
                         "EmailAddress varchar(50));"
    
    MsgBox "A table named Employees has been added to the MVA.mdb database"
    
    Set conEmployees = Nothing
End Sub

Add a reference to your most recent ADO driver using the References dialog box
Execute the application
Click OK then close the form
Delete the whole code of the Load event

The Recordset Object

The group of records that belong to a table is called a set of records. In ADO, whenever you need to deal with the set of records of a table, you should first declare a variable that would hold the records. To do this, you must obtain an appropriate reference to ADODB. A set of records of a table in ADO is managed by an object called Recordset.

After obtaining a reference to a Recordset object, in order to use it, you must open it. This is done using its Open() method whose syntax is:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The first argument to this method can be a different thing depending on the circumstance. For example, if you are trying to deal with the records of a table, this argument can be the name of that table.

The second argument specifies how the connection will be carried. It can be the name of a Connection reference you previously established. The rest of the arguments are optional. In other words, if you don't specify their values, they would use a default.

The third argument specifies the type of cursor that would be used. The available options are stored in an enumerator called CursorTypeEnum and they are:

Constant	Value	Description
adOpenUnspecified	-1	Does not specify the type of cursor
adOpenForwardOnly	0	Uses a forward-only cursor Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset
adOpenKeyset	1	Uses a keyset cursor Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible
adOpenDynamic	2	Uses a dynamic cursor Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them
adOpenStatic	3	Uses a static cursor, which is a static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible

The default value of this argument is adOpenForwardOnly.

The fourth argument specifies the type of locking system that would be used on the set of records. The available options are stored in an enumerator called LockTypeEnum and they are:

Constant	Value	Description
adLockUnspecified	-1	Does not specify a type of lock. For clones, the clone is created with the same lock type as the original
adLockReadOnly	1	Indicates read-only records. You cannot alter the data
adLockPessimistic	2	Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing
adLockOptimistic	3	Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method
adLockBatchOptimistic	4	Indicates optimistic batch updates. Required for batch update mode

The last argument is an option you specify about the first argument.

Record Creation

One of the actions you can perform using a Recordset object is to perform data entry, that is, to create new records. To support data entry, the Recordset object is equipped with the AddNew() method. Its syntax is:

recordset.AddNew FieldList, Values

The first argument is a name of a column and must be identified as belonging to an array called Fields. After specifying the name of the column in the parentheses of the Fields array, you can assign it the desired value. You can do this for each column or for selected columns of the table.

After creating a record, you should remember to save it. This is done by calling the Update() method of the Recordset object.

Practical Learning: Performing Data Entry

Design the form as follows (the names of the text boxes are displayed in them; the left command button is named cmdSubmitRecord):

Double-click the Submit Record button and implement its Click event as follows:

Private Sub cmdSubmitRecord_Click()
    Dim rcsEmployees As New ADODB.Recordset
    Dim conEmployees As New ADODB.Connection
    
    conEmployees.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
                      "Data Source='C:\ADO Lessons\MVA2\MVA.mdb'"
    rcsEmployees.Open "Employees", conEmployees, adOpenStatic, adLockOptimistic
    
    If rcsEmployees.Supports(adAddNew) Then
        With rcsEmployees
            .AddNew
            .Fields("EmplNumber") = Me.txtEmployeeNbr.Text
            .Fields("DateHired") = Me.txtDateHired.Text
            .Fields("FirstName") = Me.txtFirstName.Text
            .Fields("LastName") = Me.txtLastName.Text
            .Fields("Department") = Me.txtDepartment.Text
            .Fields("EmailAddress") = Me.txtEmailAddress.Text
            .Update
        End With
        
        MsgBox "A new record has been added to the Employees table"
    End If
    
    Me.txtEmployeeNbr.Text = ""
    Me.txtDateHired.Text = ""
    Me.txtFirstName.Text = ""
    Me.txtLastName.Text = ""
    Me.txtDepartment.Text = ""
    Me.txtEmailAddress.Text = ""
    Me.txtEmployeeNbr.SetFocus

    rcsEmployees.Close
    Set rcsEmployees = Nothing
    conEmployees.Close
    Set rcsEmployees = Nothing
End Sub

Execute the application and create a new record:
After creating a new record, click the Submit Record button:
In the same way, create a few more records

Record Insertion

Besides, or as opposed to, adding a new record, if you don't want to formally use the Recordset class to create a record, you can use the Execute() method of the Connection object. To create a new record using the Connection object, you can call the Execute() method and pass it a string that contains the following formula:

INSERT TableName VALUES(Name1, Name2, Name_n)

INSERT INTO TableName VALUES(Name1, Name2, Name_n)

Practical Learning: Inserting a new Record

Add a new command button to the right of the first
Change its Caption to Insert Record and its Name to cmdInsertRecord

Double-click the button and implement its Click event as follows:

Private Sub cmdInsertRecord_Click()
    Dim conEmployees As New ADODB.Connection
    Dim strSQL As String
    
    conEmployees.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
                      "Data Source='C:\ADO Lessons\MVA2\MVA.mdb'"
    
    strSQL = "INSERT INTO Employees VALUES ('" & _
             txtEmployeeNbr.Text & "', '" & _
             txtDateHired.Text & "', '" & _
             txtFirstName.Text & "', '" & _
             txtLastName.Text & "', '" & _
             txtDepartment.Text & "', '" & _
             txtEmailAddress.Text & "')"
    
    conEmployees.Execute strSQL
    MsgBox "The Employees table has been updated with a new record"
    
    Me.txtEmployeeNbr.Text = ""
    Me.txtDateHired.Text = ""
    Me.txtFirstName.Text = ""
    Me.txtLastName.Text = ""
    Me.txtDepartment.Text = ""
    Me.txtEmailAddress.Text = ""
    Me.txtEmployeeNbr.SetFocus

    conEmployees.Close
    Set conEmployees = Nothing
End Sub

Execute the application and enter a new value in each text box then click Insert Record
Return to your programming environment


Previous	Copyright © 2001-2005 FunctionX, Inc.	Next