Class: JetRecordset in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

Working with Microsoft Jet recordsets with DAO in VBA and VB6.

With this class you can open and close recordsets on a variety of data sources, and get advanced information about records and fields.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CJetRecordset class.
Consistent Property Get the class recordset's Consistent setting. Consistent recordsets only allow proper updates on multi-table joins. Set this property to True to allow only proper updates. Set to False to allow inconsistent updates. For more information, search DAO online help for "OpenRecordset".
Database Property Get a handle to the current open database. Use this property to tell the class which database object you want to use. You must first open the database object in your code, and then pass it to this class. The database property also returns the DAO database object of the database the class currently has open. This allows you to gain access to any of the properties and methods available for the database object that refers to the currently open database. If you close the database object in your code, the objects in this class are no longer valid. For this reason, close any instances of this class before closing your database object.
DataSource Property Get the data source for the current recordset. For more information on what the Jet Engine accepts for this property, search DAO online help for Openrecordset and look for the Source parameter.
FullyUpdatable Property Determine if the recordset and all its all fields are updatable. Non-updateable recordsets are caused by many conditions such as joins in dynasets that restrict changes or permissions settings that disallow editing.
LockType Property Get the type of locking on the recordset. Microsoft Jet supports several types of locking styles. Set this property the desired locking type to be used by the next OpenRst method call. The types of locking currently supported are:
  1. cjrLockTypePessimistic: Use pessimistic locking. This style attempts to lock the record upon entering edit mode. If a lock cannot be obtained, the edit fails. This is the default mode of the class.
  2. cjrLockTypeOptimistic: Use optimistic locking. With this type of locking, a lock is only placed when the record is updated.
  3. cjrLockTypeReadOnly: Open as read only with no locks.
For more information, search DAO online help for "OpenRecordset" method and see the "LockEdits" parameter.
PercentPosition Property Get the percent position property of the current record in the recordset.
ReadOnly Property Get the read-only state of the current class recordset. For more information, search DAO online help for "OpenRecordset" and look for the Options parameter.
RecordCount Property Get the number of records in the recordset.
RecordNumber Property Get a pseudo-record number using the AbsolutePosition property.
Note: The concept of a record number has little validity in set-oriented relational databases. This number should be used for display purposes only. Never rely on the value of a record number for navigation purposes.
Recordset Property Get the current recordset object. This property is read-only. To set the class recordset, use the class OpenRst method.
RecordsetType Property Get the type of the current recordset. Set this property before calling the OpenRst method. If you do not specify a specific type, Microsoft Jet will open whatever type of recordset it considers to be the most efficient for the data source you have specified. For more information on recordset types, search DAO online help for "OpenRecordset".
RecordSizeInBytes Property Get an approximation of size, in bytes, of a record in the recordset by determining the amount of storage for each field. This number is approximate because the storage used for memo and long binary fields is variable. Additionally, even though Boolean fields (yes/no) only use one bit of storage, this property returns its value in bytes, so each boolean field is considered as one byte. This value does not include the actual storage space used by memo and long binary fields.
Class_Initialize Initialize Set initial values to defaults which may be overridden with property settings.
CloneRecordset Method Get a clone of the current recordset. Note that this recordset should be a different object from the recordset object originally opened with this class.
CloseRst Method Close the current recordset. If the recordset was opened outside of the class and subsequently passed to this class, this method does not close the recordset.
GotoLastModified Method Move to the record that was last changed or added. Use the LastModified property with table and dynaset type recordset objects. A record must be added or modified in the recordset object itself in order for the LastModified property to have a value.
OpenRst Method Open a recordset with the specified options. To set the attributes for the recordset, set the appropriate properties for this class before calling this method.
RecordsetFieldsToArray Method Populate an array with a list of fields in the current recordset.
RestoreBookmark Method Restores the recordset to the position saved by the SetBookmark method. Use the SetBookmark method to save a bookmark at the current recordset position. Then you can use the RestoreBookmark method to return to that position.
SetBookmark Method Set a bookmark to the current record in the recordset. You can then use the RestoreBookmark method to restore to that record. This method uses the DAO BookMark property. Not all recordsets support bookmarks, so this method may not be available. For example, if a recordset is not based entirely on data sources that support unique bookmarks, the entire recordset does not support bookmarks.
BuildOptions Private Calculates the value for the m_lngRecordsetOptions variable according the settings of the m_fOption... variables. This private internal method takes all class properties related to Recordset options and converts them to a long integer bitmask used by the DAO OpenRecordset method. For more information on the bitmask, search DAO online help for OpenRecordset and look at the Options argument.
' Example of the CJetRecordset Class
'
' To use this example:
' 1. Create a new user form.
' 2. Create a command button called cmdTest
' 3. Paste the entire contents of this module into the new form's module.

' This example assumes that the sample files are located in the folder named by the following constant.
Private Const mcstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"

Private Sub cmdTest_Click()
  Const cstrSampleDatabase As String = mcstrSamplePath & "SAMPLE.MDB"
  Const cstrSampleTable As String = "Custoemrs"

  Dim TestRecords As CJetRecordset
  Dim dbs As DAO.Database
  Dim rstClone As DAO.Recordset
  Dim aFields() As String
  Dim intCount As Integer
  Dim intCounter As Integer
  Dim strTmp As String

  ' Open the sample database
  Set dbs = DAO.DBEngine.OpenDatabase(cstrSampleDatabase)
  Debug.Print "Opened the sample database."

  ' Instantiate the class
  Set TestRecords = New CJetRecordset
  Debug.Print "CJetRecordset class instantiated."

  ' Associate the class with the database
  Set TestRecords.Database = dbs
  Debug.Print "Database(): set the class to our database."

  ' We want to base our recordset on the Customers table
  TestRecords.DataSource = "Customers"
  Debug.Print "DataSource(): set the value to the [" & cstrSampleTable & "] table."

  ' Open the recordset as a dynaset
  TestRecords.RecordsetType = cjrType_Dynaset

  ' Set the ReadOnly property to True
  TestRecords.ReadOnly = True

  ' Set the LockType to Optimistic
  TestRecords.LockType = cjrLockTypeOptimistic

  ' Set the Consistent/Inconsistent property
  TestRecords.Consistent = True

  ' Open the recordset
  TestRecords.OpenRst
  Debug.Print "OpenRST(): opened the recordset in readonly mode"

  ' Move to the first record
  TestRecords.Recordset.MoveFirst

  ' Try and go into Edit mode. This should fail because we opened the recordset with the ReadOnly option.
  On Error Resume Next
  TestRecords.Recordset.Edit
  If (Err.Number <> 0) Then
    Debug.Print "Because we opened the recordset in readonly mode, the call to the Edit method failed with the following error: " & Err.Description
  End If
  On Error GoTo 0

  ' Close the recordset
  TestRecords.CloseRst
  Debug.Print "CloseRST(): closed the recordset."

  ' Turn the readonly property off
  TestRecords.ReadOnly = False

  ' Re-open the recordset
  TestRecords.OpenRst
  Debug.Print "OpenRST(): opened the recordset in read/write mode."
  ' Let's get the count of records
  Debug.Print "RecordCount is: " & TestRecords.RecordCount

  ' Move to the fifth record. Note that recordsets are 0-based, meaning that the first record is record 0.
  ' So to move to record five, we actually specify the value 4.
  TestRecords.Recordset.Move 4

  ' Show the record number
  Debug.Print "RecordNumber is: " & TestRecords.RecordNumber

  ' Show the approximate percent position
  Debug.Print "PercentPosition is: " & TestRecords.PercentPosition

  ' Now save the position
  TestRecords.SetBookmark

  ' Move to the first record
  TestRecords.Recordset.MoveFirst

  ' Restore the saved position
  TestRecords.RestoreBookmark

  ' Let's see if it worked
  Debug.Print "RecordNumber is now: " & TestRecords.RecordNumber

  ' Create a clone of the recordset
  Set rstClone = TestRecords.CloneRecordset
  rstClone.MoveLast
  Debug.Print "The clone has " & rstClone.RecordCount & " records."
  rstClone.Close
  Set rstClone = Nothing

  ' Get and display an array of field names of the recordset
  intCount = TestRecords.RecordsetFieldsToArray(aFields, True)
  Debug.Print "There are " & intCount & " fields returned to the array."
  For intCounter = 0 To intCount - 1
    strTmp = strTmp & aFields(intCounter) & " | "
  Next intCounter
  Debug.Print "Fields are: " & strTmp

  ' Determine if the recordset is updatable.
  Debug.Print "The current recordset " & IIf(TestRecords.FullyUpdatable, "is ", "is not ") & "fully updatable."

  ' Show the approximiate record size
  Debug.Print "This recordset uses approximately: " & TestRecords.RecordSizeInBytes & " bytes of storage."

  ' Close the class
  Set TestRecords = Nothing

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS