Index Next

Initializing a Record Set

Initializing a Record Set From a Table or Query

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.

The Database factor must represent a valid database. It can be the current database or another one.

the Source, is the only required argument of this method. It 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.

It can be used to create a record set from the current database or from a closed database.

Opening a Record Set

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

All arguments are optional.

·        you can call the method without any argument using the following syntax:

recordset.Open

·        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

The third argument specifies the type of cursor that will manage the access to the record set.

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

My example – tested and working one

With SQL

Private Sub cmdAnalyzeSSK_Click()

    'declaring variable for openin recordset

    Dim rstSSK As ADODB.Recordset

    'declaring variable for field

    Dim fldEach As ADODB.Field

    Set rstSSK = New ADODB.Recordset

    rstSSK.Open "SELECT [tblSSK].[ad], [tblSSK].[soyad], [tblSSK].[Meslek], [tblSSK].[KimlikNo] FROM [tblSSK];", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

    'the name of each column in a message box, one at a time

    For Each fldEach In rstSSK.Fields

        MsgBox fldEach.Name

    Next

    'closing recordset

    rstSSK.Close

    Set rstSSK = Nothing

End Sub

Without SQL

Private Sub cmdAnalyzeSSK_Click()
'declaring variable for openin recordset
Dim rstSSK As ADODB.Recordset
'declaring variable for field
Dim fldEach As ADODB.Field
Dim dbssk As Object

Set rstSSK = New ADODB.Recordset
rstSSK.Open "tblSSK", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdTable

'the name of each column in a message box, one at a time
For Each fldEach In rstSSK.Fields
MsgBox fldEach.Name
Next

'closing recordset
rstSSK.Close
Set rstSSK = Nothing
End Sub