Initializing a Record Set

With the Microsoft Access library, 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 you would have declared. Normally, you would have declared this variable as a placeholder of 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 cmdRstCustomers_Click() Dim dbCustomers As Object Dim rstCustomers As Object Set dbCustomers = CurrentDb Set rstCustomers = dbCustomers.OpenRecordset("Customers") End Sub

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

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, you don't have to pass by the database object. 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.

 

Opening a Record Set

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 object 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 provide 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. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

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 object 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 = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    
    rstVideos.Open
    
    rstVideos.Close
    Set rstVideos = Nothing
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 "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection
    
    rstVideos.Close
    Set rstVideos = Nothing
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 = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    . . .
    rstVideos.Open
    
    rstVideos.Close
    Set rstVideos = Nothing
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 enumerator (a list of constant values) 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 "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic
    
    rstVideos.Close
    Set rstVideos = Nothing
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 = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    
    rstVideos.Open
    
    rstVideos.Close
    Set rstVideos = Nothing
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 enumerator named LockTypeEnum. The members of this enumerator 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 "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

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

If the source is a SQL statement as we have used it so far, this argument can be pass 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 "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdText
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

So far, we have specified 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. For example, if you pass the source as a table named Videos, the database engine would convert it into

SELECT * FROM Videos;

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
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub