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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)