With this class you can open and close recordsets on a variety of data sources, and get advanced information about records and fields.
|(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:
|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
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!
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"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