Introduction to Records

A record was created by entering a value under a column in a table or in a Windows control of a form. To identify the value held by a column, the Field class is equipped with a property named Value. Here is an example of using it:

After creating a table, you can fill it with records as we will do in the next sections and lessons. Here is an example:

The records from a table or a query are grouped in an object referred to as a set of records or called a record set. To support them, the Application of the Microsoft Access Object library or of the DAO is equipped with a class named Recordset. In the same way, to consider the records of a table or query, the ADO library is equipped with a class called Recordset.

Most of the time, you use a Recordset object without being aware because the database engine handles all or most of the necessary routines behind the scenes. In some other cases, you must create or initiate a Recordset object in order to use it. To do this, you have various alternatives. In fact, the way you create a Recordset object may depend on the library you are using at the time you need to "grab" the set of records.

When creating a record set as we will see in the next few sections, you can specify whether to use one or more tables and what record(s) would be included. How the record set is created, the number of tables, the number of records, and the types of operations that can be performed on the records lead to various types of record sets:

• If your Recordset object includes only one table, it is referred to as a Table record set
• If a record set includes one or more tables, it is called a Dynaset. This type allows adding, editing, updating, or deleting records that are part of the Recordset object
• A record set is called a Snapshot if it allows you to view the records of one or more tables, navigating back and forth in the set, but you cannot make changes to the records
• A Recordset object is referred to as Forward-Only if you can view its records without changing them but you can only move forward. This means that, if you get to a record, examine it, and move to the next record, you cannot refer back to a record you left behind
• If you create a query based on data from one or more tables and allows the users to navigate back and forth with the ability to make changes to records, this type of record set is referred to as Dynamic

The essence of a database is to hold values. The primary type of value of a table is a string. In Lesson 3, we saw that a string is a character or a combination of characters. You can deal with strings at various levels, on a column of a table or a control on a form or report.

The most regular object to configure text is on a column of a table. You can configure a column for text while a table is either in Datasheet View or in Design View. To start a table and easily create a new column, display the table in Datasheet View:

To create a new column on a table in Datasheet View, you can double-click Add New Field and type a name for the new column. When you create a new column of a table in Datasheet View, the column is automatically made for text. If the column has already been created and it was made for a type other than text but you want to change it for text, click the column header or any of its cells. In the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Text.

You can use one of the built-in text-based fields to create a new column. To start, display the Field Templates window by clicking New Field from the Ribbon. From the Field Templates, use one of the following:

• Basic Fields: Single Line of Text
• Assets: Item, Manufacturer, or Model
• Contacts: Company, Last Name, First Name, E-mail Address, Job Title, Business Phone, Home Phone, Mobile Phone, Fax Number, City, State/Province, ZIP/Postal Code, or Country/Region
• Events: Title or Location
• Issues: Title
• Projects: Project Name
• Tasks: Title

You can use a table in Design View to make a field handle text. To do this, specify the name of a column and set its Data Type as Text. Here is an example:

A Text data type allows the user to type any kind of characters or group of characters.

We saw that each library available in Microsoft Access provides its own means of creating the columns of a table.

1. Start Microsoft Access and create a blank database named cpar1
2. Close the default table without saving it
3. On the Ribbon, click Create
4. In the Forms section, click Form Design
5. Right-click the form and click Form Header/Footer
6. Save the form as RepairOrders

We saw how to create a table with a column in the Microsoft Access Object Library. We saw that we could start the table as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", DataType, Size)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

We saw that we must call the CreateField() method. The second argument, which is required, must be the data type applied on the column. To create a text-based field, the second argument can be dbText. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

You can also specify the data type as DB_TEXT. The dbText or DB_TEXT type is equivalent to the Text data type used in Microsoft Access.

We have an introduction to table creation in the Microsoft DAO Library. We saw that we could start the table as follows:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("Exercise.accdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = _
	tblEmployees.CreateField("EmployeeNumber", DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

The second method to the CreateField() method specifies the type of values that would be entered into the column. If you are creating a text-based column, pass this argument as DB_TEXT or dbText. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("Exercise.accdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT)
    tblEmployees.Fields.Append fldEmployeeNumber

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

As seen in previous lessons, one of the main ways you use SQL in your database is through the DoCmd class. You can access the DoCmd class either from the Microsoft Office Access Database Engine Object Library (Access.Application.12) or as a property of the Application class of the Microsoft Access Object library.

In Lesson 10, we saw that the formula of creating a table in SQL is:

CREATE TABLE TableName(Column1, Column2, Column_X)

And we saw that the actual formula of creating a column is:

ColumnName DataType Options

We also saw how to specify the name of a new column. Here was our example:

Private Sub cmdCreateTable_Click()
   DoCmd.RunSQL "CREATE TABLE Customers(FirstName . . .);"
End Sub

After the name of the column, you must specify the data type of the column. If you are creating a column whose value would be string-based, you can apply the TEXT, the CHAR, or the VARCHAR data types. Here is an example:

Private Sub cmdCreateTable_Click()
   DoCmd.RunSQL "CREATE TABLE Customers(FirstName Text);"
End Sub

In the same way, you can create as many columns as you want by separating them with commas. Here are examples:

Private Sub cmdMicrosoftAccess_Click()
   DoCmd.RunSQL "CREATE TABLE Customers(FirstName Text, LastName Char);"
End Sub

If you are planning to use a long SQL statement to create a table, you can either use the line continuation operator to span the statement on various lines, or you can declare a String variable, store the statement in it, and then pass that variable to the DoCmd.RunSQL() method.

Each one of the text, char, or varchar data types would produce the same effect.

1. As the form is still displaying in Design view, right-click the Detail bar and click Build Event...
2. In the Choose Builder dialog box, double-click Code Builder
3. Implement the event as follows:
    

   Private Sub Detail_Click() DoCmd.RunSQL "CREATE TABLE RepairOrders(CustomerName text, " & _ "CustomerAddress text, " & _ "CustomerCity varchar, " & _ "CustomerState char, " & _ "CustomerZIPCode char, " & _ "CarMakeModel varchar, " & _ "CarYear char);" End Sub

4. Return to Microsoft Access and switch the form to Form View
5. Click the middle of the form to create the table
6. Return to Microsoft Visual Basic
7. Delete the whole code of the Detail_Click event
8. Return to Microsoft Access
9. Right-click the tab of the form and click Design View to change the view
10. In the Properties window, click the Data tab
11. Set the Record Source to RepairOrders
12. In the Tools section of the Ribbon, click Add Existing Fields
13. Using the items from the Field List, add the columns and design the form as follows:
     
    
14. Save the form

The binary data type can let a field accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when programmatically creating a column, using the Microsoft Access Object Library, specify its data type as DB_BINARY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblBooks As Object
    Dim colShelf As Object

    Set curDatabase = CurrentDb
    Set tblBooks = curDatabase.CreateTableDef("Books")
    
    Set colShelf = tblBooks.CreateField("Shelf", DB_BINARY)
    tblBooks.Fields.Append colShelf
    
    curDatabase.TableDefs.Append tblBooks
End Sub

If you are creating the table in DAO, set the column's data type as dbBinary.

If you are programmatically creating the column in ADO, set its data type as either BINARY or VARBINARY.

If you are creating a column that will hold external documents, such as pictures, formatted (from Microsoft Word for example), or spreadsheet, etc, and if you are working in the table's Design View, set the column's data type to OLE Object.

If you are programmatically creating the table using SQL and if you are creating a column that would hold embedded objects, set its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.

With the Microsoft Access Object Library or DAO, to create a Recordset object using a table or a query that is associated with a database, you can call the OpenRecordset() method of that database. The syntax of this method is:

Set Variable = Database.OpenRecordset(Source[, Type [, Options [, Lockedits ]]])

The Variable factor can be an Object variable you would have declared as a placeholder for a Recordset object. The Database factor must represent a valid database. It can be the current database or another one. The only required argument of this method is the Source, which is passed as a string. This can be the name of a table or a query. Here is an example:

Private Sub cmdGetVideos_Click()
    Dim dbVideoCollection As Object
    Dim rstVideos As Object

    Set dbVideoCollection = CurrentDb
    Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

    Set rstVideos = Nothing
End Sub

When this method executes, it retrieves the records stored in a table or a query named Videos and creates a record set from it.

In Lesson 23, we will see that, instead of passing the whole table or query to the OpenRecordset() method, you can pass a SELECT statement. Here is an example:

Private Sub cmdGetVideos_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Blah Blah Blah")

    Set rstVideos = Nothing
End Sub

This time, only a few columns would be considered in the Recordset object.

The above code supposes that you would go through a database to create a record set. It can be used to create a record set from the current database or from a closed database. If you are working in a database that has its own objects and you want to create a record set using one of these objects, each database object that can act as a data source, including tables and queries, is equipped with an OpenRecordset() method. Its syntax is:

Set Variable = object.OpenRecordset([Type [, Options [, Lockedits ]]])

Notice that, this time, you specify neither the database nor the name of the object. Here is an example

Private Sub cmdRstNames_Click()
    Dim curDatabase As Object
    Dim rstCustomers As Object
    Dim tblCustomers As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblCustomers = curDatabase.TableDefs("Customers")
    ' Create a Recordset object from the specified table
    Set rstCustomers = tblCustomers.OpenRecordset
End Sub

When this code runs, it retrieves the records of a table named Customers and creates a record set from it.

Consider the following table:

Earlier, we saw that, when creating a record set, you may need to take various factors into consideration. To consider those factors, the Recordset class of the ADO library is equipped with the Open() method. Its syntax is:

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

All arguments are optional. Instead of calling it and providing the argument, you can first specify each factor using the appropriate property as we will see shortly. Then, after specifying the properties, you can call the method without any argument using the following syntax:

recordset.Open

The recordset factor can be a Recordset variable that you have previously declared. The first argument, Source, can be a SQL statement that will be used to select the records. The source can also include an optional WHERE condition that specifies what records would be considered.

Instead of calling the Open() method version that expects the argument, you can first specify the source of data. To support this, the Recordset class is equipped with a property named Source that is of type String and that can receive the string of the data source. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "Blah Blah Blah"
    
    rstVideos.Open
End Sub

The second argument of the Recordset.Open() method, ActiveConnection, specifies the connection through which the record set will be accessed. It can be a connection as those we have seen in the previous lessons. Here is an example that uses the connection to the current database:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Blah Blah Blah", _
                   Application.CodeProject.Connection
End Sub

Instead of passing the connection to the Recordset object, you can specify it as its own object. To support this, the Recordset object is equipped with a property named ActiveConnection and that is of type Connection. It also can be a valid connection as those we have used so far. Here is an example:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "Blah Blah Blah"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    . . .
    rstVideos.Open
End Sub

The third argument specifies the type of cursor that will manage the access to the record set. The available cursors that we reviewed earlier are members of an enumeration named CursorTypeEnum. The cursor argument can hold one of the values of cursors we saw earlier. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Blah Blah Blah", _
                   Application.CodeProject.Connection, _
                   adOpenStatic
End Sub

If you want, you can specify the cursor separately. This is possible because the Recordset object provides a property named CursorType that is of type CursorTypeEnum. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "Blah Blah Blah"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    
    rstVideos.Open
End Sub

The fourth argument, LockType, represents the type of locking system that will be applied on the record set. The available types are stored in an enumeration named LockTypeEnum. The members of this enumeration are those we reviewed earlier. Here is an example of passing the fourth argument:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Blah Blah Blah", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
End Sub

The last argument is used to identify the type of the Source argument. The values of this argument are members of an enumeration named CommandTypeEnum.

If the source is a SQL statement as we have used it so far, this argument can be passed as adCmdText. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Blah Blah Blah", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdText
End Sub

So far, we have considered the Source factor as a SQL statement. ADO allows you to use the name of a table as the source. If you do this, the database engine would complete the Source with a SELECT statement.

If you pass the Source factor as the name of a table, then the last argument of the Open() method can be passed as adCmdTable. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
End Sub

After using a Recordset object, you should (strongly) close it. To close a record set, you can call its Close() method. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
End Sub

After closing the record set, you should release the resources it was using and make them available to other applications that would need them. To do this, assign the Nothing value to the Recordset object. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

We saw that, after using a connection, you should also close it by calling its Close() method and you should release its resources. If you were using the ActiveConnection property to connect to the current database, to close it, the Recordset object is equipped with a property named ActiveConnection. Before closing the Recordset object, you should first assign Nothing to its ActiveConnection property.

To work on a record set, you must communicate to the database engine what rules your set will follow to be considered as such. In previous lessons, we saw how to create a table and its columns. We also know already how to populate a table with data. Once the table has been created, it is considered a record set. You can use it as the contents of a Recordset object.

After creating a record set, you may want to know the actual number of records it contains. This information is stored in a property named RecordCount. On a form, to get the number of records it contains, you can access the RecordCount property of its RecordsetClone object. This could be done as follows:

RecordsetClone.RecordCount

This would produce the total number of records that a form holds. In the same way, the Recordset object of the ADO library provides the RecordCount property.

So far, that is, in the previous lessons, we have been concerned with one database accessed by one user. In many environments, a database will need to be accessed by more than one computer. This means that, when creating a Recordset object, you need to keep different factors in mind. For example, you (actually your user) may be accessing a record or a series of records at the same time with someone else from another computer. In some cases, there may usually be only one person using a database and there might occasionally be someone else but unlikely, although possible. In some other cases, such as on the Internet or in a big enterprise, there might be many people accessing, or trying to access, a database, or a specific set of records, at the same time.

Imagine you are working on a large database such as a bank application that has thousands or millions of records (such as thousands or millions of customers). If you want to perform an operation on the customers, you may have to deal with many or all records. You may also have to deal with the fact that other people are accessing the same records like you, at the same time.

Normally, some operations don't require you to have access to all records, at least not all the time. When working on records, thus when creating a Recordset object, you can specify a way to isolate a range of records and deal only with that range. The range of records that you select is called a cursor.

Because a cursor plays a tremendous role in a record set, there are different options when using it. To support these options, there are various types of cursors:

• A static cursor holds a constant set of records. Suppose you create a record set and open it. Also suppose that either you only or other people besides you are working on the same record set. You get to a record and start viewing it (or even working on it). After using that record, you move to another record, and you can do this back and forth as you wish. Suppose that, while doing this back and forth navigation (we will learn later on how to programmatically navigate through a record set), another person has accessed a record that is part of your record set and made a change. If using a static cursor, every time you visit the record set, it shows the same records the way they were when you opened the record set. It would not show the changes that have taken place. This is why it is called a static cursor. A static cursor is appropriate if you are not interested to know what changes have taken place ever since you opened the record set.
  In ADO, a static cursor is represented with the adOpenStatic constant.
• A cursor is referred to as forward-only if it allows you to move forward through the records. Here is how it works. Suppose that you create a Recordset object and specify its cursor. Suppose that, while using the record set, you get to a record that was set as the start point of your cursor. Also, suppose that either you only or other people besides you are working on the same record. If you make a change on the current record, the other people will be notified. If other people make a change on the current record, you also would know. After using that record, you move to the next. With the forward-only cursor, you cannot move back to a record you left already. This means that, even if you are still working on the record set, if there are changes performed on a record you left behind (for example, if another person who is working on the same record changes something on a record that you passed already), you cannot know and you cannot find out because you cannot go back to a record left behind. If this becomes a necessity, you can close the cursor and re-open it. A forward-only cursor is appropriate if you don't need to navigate back and forth among the records of a record set. Because of the way it works, if you access the RecordCount property of a forward-only cursor, it would produce -1.
  In ADO, this type or cursor is represented by the adOpenForwardOnly constant.
• A cursor is called dynamic if it detects and shows all changes that are occurring in a record set, whether the changes are caused by you or by other people who are accessing the record set at the same time. This type of cursor is appropriate if you want to know, live, what is going on with the record set you are working with.
  In an ADO database, to specify a dynamic cursor, use the adOpenDynamic constant.
• A key set cursor creates and saves a key for each record that has been modified since the record set was opened. If you access the record, the key is used to check the data of the record set.
  A key set cursor is created using the adOpenKeyset constant.
• If you don't want to specify a cursor when creating a record set, you can use the adOpenUnspecified constant.

We introduced database connections and showed different ways of creating one. When creating a record set, in some cases, especially when using ADO, you may need to specify what connection the Recordset object would use.

Imagine that, after creating a record set and working on it, you want to control who else can have access to the records of the set you are using. To exercise this control, you can create a "lock". This allows you, for example, to prevent other people from changing the records until you have finished with them. To support locking, the ADO library provides various options:

• When a computer connects to a database, its user may need to make changes to various records at the same time, such as deleting a range of records or changing many records at the same time (such as giving a raise to many employees), instead of making one change, then another, then another. For this type of scenario, when the user accesses the records, instead of monopolizing them and waiting for the user to finish an operation that could take long, you can download the records on the user's computer, and disconnect the user from the database. The user would then make the necessary changes. When the user is ready to commit the changes, you can then reconnect to the data source and submit the changes. This type of lock is referred to as batch optimistic. ADO supports this through a constant value named adLockBatchOptimistic
• You may have a database that a few different people access at the same time. If the database is small enough, which is the case for restricted environment, the likelihood of two people editing or updating the same record (at the same time) may be low. In this case, you can indicate that you want to lock the record only when necessary. In this case, you use what is referred to as optimistic locking. This is implemented using the adLockOptimistic value
• The above two options assume that you would lock many records to apply the indicated scenarios. If you prefer to lock one record at a time, you can use what is referred to as pessimistic locking. This is done using the adLockPessimistic constant
• The above three scenarios allow a user to edit and/or update the records that are included in the set. In some cases, you may want to prevent any editing or update on the records while the set is being accessed. In this case, you can set the records to read-only. To do this, you can lock the set using the adLockReadOnly lock
• If you don't want to specify the type of lock system to use on a record set, use the adLockUnspecified value