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

Properties and methods for working with data stored in Jet/Access databases using DAO for VBA and VB6.

When you use VB6 to develop database applications, you have several choices for local database access:

  1. Use the Data Control to access Jet/Access databases.
  2. Write DAO code to work against Jet/Access databases.
  3. Use DAO/Jet with linked tables for heterogeneous data access to disparate data sources such as FoxPro, xBASE and Excel.
    The functionality in this class enhances method #2, the use of DAO and Jet in program code to work with data.
Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CJetData class.
Database Property Get the DAO Database object of the currently opened database which you can use to get and set database properties. Don't close the database object through the Database property using the DAO close method because class will no longer have the necessary state to perform operations. Instead, perform the CloseDB method if you had the class open the database, or close your own database variable that was passed to the class.
LastErrorDescription Property Get a description of the last error that occurred (this is the same as the first member in the DAO.DBEngine.Errors collection). This value is useful while debugging to determine why an operation failed. This value is the same as the first member in the DAO.DBEngine.Errors collection.
LastErrorNumber Property Get the error number of the last error that occurred (the same as the Number property of first member in the DAO.DBEngine.Errors collection. This value is useful while debugging to determine why an operation failed. This value is the same as the Number property of first member in the DAO.DBEngine.Errors collection.
Path Property Get the path of the open database, without the file name or extension.
Note: You can use the Name property of a DAO.Database object to retrieve the full path, name and extension of a database.
Version Property Get the version of Jet used to the create the database. When Jet is called on to create a database, it creates a property in the new database called Version. This property stores the version of the Jet Engine that was used to create the database. For example, if you create a database with Visual Basic version 3, or Access version 2, version 2 of the Jet Engine is used, and the Version property is 2.
Note: Jet versions 2 and 3 allow you to create databases compatible with previous versions. For example, you can use Jet 3 to create a Jet 1.x database. In such a case, the Version property returns 1.x, since the database is considered a 1.x database.
BrowseTableIndexOrder Method Process the records in a table in the order of the specified index. The purpose of this function is to illustrate how to move through a table in index order.
ChangeColumnCase Method Change the case of a field for all records in the specified table by modifying the records individually. A more efficient alternative would be to use an Update Query, but this example is useful if you want to perform more complicated tasks for each record.
ChangeDataSequential Method Search for data in a field and change its value. This method changes data values in a non-keyed table by opening a recordset on the data source and searching through the records for a match. When a match is found, the specified fields is changed to the specified value.
Note: The method explicitly opens a Dynaset type recordset to ensure the FindFirst and FindNext methods are available.
ChangeValueInKeyedTable Method Example of using the SEEK command to locate and change a value in a table with a primary key. This method uses the Index property of a DAO recordset and the Seek method to find the first match of the specified value. If a match is found, it changes the value in the specified field to the specified value. This method can only be used on recordsets of the Table type. This is because table type recordsets are the only type that support direct access to indexes using the DAO Seek method. For information on using the Seek method on linked (attached) tables, see the SeekLinkedTable method in this class.
CloseDB Method Close the current open database.
Note: This only works if the class opened the database through the OpenDB() method. If the class has a database open that was passed to it through the Database property, or the OpenDB() method has not yet been called, this method silently fails.
DomainAverage Method Get the arithmetic mean of a set of values in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainCount Method Get number of records in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainFirst Method Get the first occurrence field value in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainLast Method Get the last occurrence field value in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainLookup Method Get a field value in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainMax Method Get the maximum value of a field in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainMin Method Get the minimum value of a field in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainStDev Method Get the standard deviation of a field in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainSum Method Get the Sum of a field in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
DomainVar Method Get the variance of a field in a specified set of records. This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
EmptyTable Method Delete all records from the named table by running a SQL Delete query on the specified table.
Note: Mass changes on table data, such as deleting all records, leaves databases in a fragmented state. Be sure to run the Jet Engine's Compact method after such changes for optimal performance.
ExportTableToText Method Export the data from a table to a delimited text file. The data is output to an ASCII text file with one line per record. Note that this procedure does not export complex field types such as attachment or multivalue fields. Use the modules modAttachmentFields and modMultiValueFields to manage those field types.
ExportTableMultiValueToText Method Export the data from a table to a delimited text file including support for multivalue fields in ACCDBs.
FindDuplicates Method Create a table with all the duplicate values in a field of a table or query. Note that this does not apply to multivalue complex fields in ACCDBs. This procedure creates a table called 'Duplicates in xxx' where xxx is the name of the table specified. This table contains one record for each duplicate value in the specified table. Use the strField parameter to specify which field is to be searched for duplicates. For example, if you have a Customers table with a field called LastName, specify the LastName field as the strField parameter to find all records with duplicate last names. Before running this method, ensure that the output table does not already exist in the database.
FindRandom Method Get the value of the specified field from a randomly selected record. FindRandom works by generating a random number to match the table's record number. It does this by multiplying the number of records in the table by the random number, adding one, and then converting the resulting value to a long integer to truncate decimals. It then uses the Move method to go to that random record.
GetTableSummary Method Calculate the summary specified for a field in a table. This procedure is similar to the Domain... procedures, but it wraps the most common functionality into one method.
IsRecordLocked Method Determine if the current record in the passed recordset is locked by checking the recordset's LockEdits property. If True, the recordset is using Pessimistic locking which means the record is locked as soon as Edit mode is invoked. If LockEdits is False, the recordset will not be locked until the Update is issued, in which case we can't determine the locked status of a record. It then checks the EditMode property to see if an edit is in progress, which means the record is locked.
IsTableEmpty Method Determine if the named table is empty (no records) by opening a dynaset-type recordset on the specified table. It then checks EOF and BOF properties to determine if the table contains any records.
OpenDB Method Open the named Access/Jet database with the options specified. Use this method before calling any of the class's database specific routines. You can also point the class to a database by opening a database and setting this class's Database property to the opened database.
RankTable Method Assign the rank of each record into the specified double field. Tied records are averaged. For instance, if records 3, 4, 5 and 6 are tied, their rank is 4.5.
RecordCount Method Get the number of records in a table or returned by a query. This procedure uses a SELECT COUNT query to count the total records in the specified data source. The data source can be a table or query. If a query is specified, the query must be row-returning.
Note: The use of the SELECT COUNT (*) SQL syntax. This form tells the engine to consider all fields in all records which allows for certain internal optimizations that make the query run fast.
SearchAllFields Method Get the number of occurrences of the specified text in all fields of all records of the specified table or query. This procedure opens a dynaset-type recordset on the named table or row-returning query and loops through all fields in all records searching for the specified value.
SeekLinkedTable Method Using Seek/Indexes on a linked table. Using the DAO Seek method is generally the fastest way to find a specific value in a table because it uses the tables index(s) to search on. See CZipCode_DAO for an example of this. Unfortunately, the Seek method is only available on Table-type dynasets. And since you cannot open a table-type dynaset on a linked/attached table, there is no obvious way to use the Seek method on linked/attached tables.
This procedure shows how to get around the problem. It does the following:
  1. Interrogates the Connect property of the specified linked table.
  2. Determines the path and name of the database where the linked table actually resides.
  3. Opens a Database object on that database.
  4. Opens a table-type recordset on the desired table through the newly opened database. Since the database object is open directly to the database where the linked table actually resides, the database object doesn't consider the table to be linked.
  5. Sets the recordset's Index property to the name of the index to search on.
  6. Uses the Seek method to find the specified value.
  7. Returns a True or False value depending on whether or not the specified value was found. Note that this procedure really doesn't do anything useful other than search for a value and illustrate the general technique. In real applications, you should modify this code to find the record(s) you want, and then perform an operation such as retrieving a field value, editing the record, etc.
GetPathFromFullPath Private Get the path without the filename from a fully qualified path. This is a class-private help function that supports the class's methods and properties.
' Example of the CJetData Class
'
' To use this example:
' 1. Create a new 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 cstrSampleTable1 As String = "Customers"
  Const cstrSampleTable2 As String = "Orders"
  Const cstrChangeTable1 As String = "DAOSample1"
  Const cstrOutputFile As String = mcstrSamplePath & "DAOTEST.TXT"

  Dim clsJetData As CJetData
  Dim dbsMine As DAO.Database
  Dim rstTest As DAO.Recordset
  Dim lngRecords As Long
  Dim fLocked As Boolean
  Dim fEmpty As Boolean
  Dim varValue As Variant
  Dim intChanges As Integer

  ' Clean up any temp files from a previous run of this example
  On Error Resume Next
  Kill cstrOutputFile
  On Error GoTo 0

  ' Instantiate the class
  Set clsJetData = New CJetData
  Debug.Print "CJetData class instantiated."

  With clsJetData
    ' Open a database through the class
    If .OpenDB(cstrSampleDatabase, False, False) Then
      Debug.Print "OpenDB(): sample database opened."

      ' Browse the Customers Table in the order of its PrimaryKey Index
      .BrowseTableIndexOrder cstrSampleTable1, "PrimaryKey"
      Debug.Print "BrowseTableIndexOrder(): browsed recordset."

      ' Change the ContactName field in the sample table to upper case
      intChanges = .ChangeColumnCase(cstrChangeTable1, "ContactName", True)
      Debug.Print "ChangeColumnCase: changed case for " & intChanges & " records."

      ' Find each occurrence of 'Mexico' in the Country field in the sample table, change the Contact Title to Marketing Manager
      intChanges = .ChangeDataSequential(cstrChangeTable1, "Country", "Mexico", "ContactTitle", "Marketing Manager", False)
      Debug.Print "ChangeDataSequential(): made " & intChanges & " changes in the sample table."

      ' Find the record in the sample table whose PrimaryKey value is
      ' ANTON, and change the value of the Contact Title field to: Marketing Manager
      If .ChangeValueInKeyedTable(cstrChangeTable1, "ANTON", "ContactTitle", "Marketing Manager") Then
        Debug.Print "ChangeValueInKeyedTable(): made changes in sample table."
      Else
        Debug.Print "ChangeValueInKeyedTable(): failed to change sample table."
      End If

      ' Use the database property to get the name of the current open database.
      ' This shows how to use a property of the class database object that is not exposed directly by the class.
      Debug.Print "The current database is: " & .Database.name

      ' Get the average value of the Freight field for records where the OrderDate is before January 1, 1995.
      varValue = .DomainAverage("Freight", cstrSampleTable2, "OrderDate<#01/01/1995#")
      Debug.Print "DomainAverage() returned " & varValue

      ' Get the count of records from the OrderDate field
      varValue = .DomainCount("OrderDate", cstrSampleTable2, "")
      Debug.Print "OrderDate() retunred: " & varValue

      ' Get the value of the Ship Via field for the first record where the order date is before January 1, 1995.
      varValue = .DomainFirst("ShipVia", cstrSampleTable2, "OrderDate<#01/01/1995#")
      Debug.Print "DomainFirst() returned: " & varValue

      ' Get the value of the Ship Via field for the last record where the order date is before January 1, 1995.
      varValue = .DomainLast("ShipVia", cstrSampleTable2, "OrderDate<#01/01/1995#")
      Debug.Print "DomainLast() returned: " & varValue

      ' Find the record with order id of 10546 and return the value for the CustomerID field.
      varValue = .DomainLookup("CustomerID", cstrSampleTable2, "OrderID=10546")
      Debug.Print "DomainLookup() returned: " & varValue

      ' Get the maximum of the Freight field where the Employee ID is 4.
      varValue = .DomainMax("Freight", cstrSampleTable2, "EmployeeID=4")
      Debug.Print "DomainMax() returned: " & varValue

      ' Get the minimum of the Freight field where the Employee ID is 4.
      varValue = .DomainMin("Freight", cstrSampleTable2, "EmployeeID=4")
      Debug.Print "DomainMin()returned: " & varValue

      ' Get the standard deviation of the Freight field where the Employee ID is 4.
      varValue = .DomainStDev("Freight", "Orders", "EmployeeID=4")
      Debug.Print "DomainStDev() returned: " & varValue

      ' Get the Sum of the Freight field where the Employee ID is 4.
      varValue = .DomainSum("Freight", "Orders", "EmployeeID=4")
      Debug.Print "DomainSum() returned: " & varValue

      ' Get the variance of the Freight field from the Orders table where the Employee ID is 4.
      varValue = .DomainVar("Freight", "Orders", "EmployeeID=4")
      Debug.Print "DomainVar() returned: " & varValue

      ' Now we will close the class's database that we opened with the call to the OpenDB method.
      .CloseDB
      Debug.Print "CloseDB(): database closed."
    Else
      Debug.Print "OpenDB(): sample database could not be opened."
    End If

    ' The class is now in a state where it is not pointing to any database
    ' so any subsequent calls to methods or properties will fail.

    ' Next, we'll open a database and pass that database to the class.
    ' This is the technique to use with the class when your application already has a database open,
    ' and needs to pass the database to the class.
    Set dbsMine = DBEngine.OpenDatabase(cstrSampleDatabase)
    Debug.Print "Opened a database on our own."

    ' Now that the database is open, let's tell the class to use it by
    ' Seting the class Database property to the database we opened
    Set .Database = dbsMine
    Debug.Print "Sent our database to the class."

    ' All subsequent calls to the class will happen through the dbsMine database.

    ' Empty the Orders table.
    If .EmptyTable(cstrChangeTable1) Then
      Debug.Print "EmptyTable(): Delete query run on sample table."
    End If

    ' Export the records in a sample table to a text file using a semicolon as the field delimiter.
    lngRecords = .ExportTableToText(cstrSampleTable1, cstrOutputFile, ";")
    Debug.Print "ExportTableToText(): " & lngRecords & " records from table exported to file."

    ' Find duplicates in the sample table based on the Contact Title field.
    ' This example creates a table called Duplicate Customers. If this table
    ' already exists in the sample database, an error is returned.
    varValue = .FindDuplicates(cstrSampleTable1, "Duplicate Customers", "ContactTitle")
    Debug.Print "FindDuplicates(): duplicates for table stored in " & varValue

    ' Pick a random Contact Title from the sample table
    varValue = .FindRandom(cstrSampleTable1, "ContactTitle")
    Debug.Print "FindRandom(): returned: " & varValue

    ' Get the average of order dates using the GetTableSummary method.
    varValue = .GetTableSummary(tsctAverage, cstrSampleTable2, "OrderDate")
    Debug.Print "GetTableSummary() returned: " & varValue

    ' Open a recordset and lock a record.
    ' Then call the IsRecordLocked method to test to see if it is locked.
    Set rstTest = .Database.OpenRecordset(cstrSampleTable1)
    With rstTest
      .MoveFirst
      .LockEdits = True
      .Edit
      ' Record is now locked
    End With
    fLocked = .IsRecordLocked(rstTest)
    rstTest.Close
    Debug.Print "IsRecordLocked(): first record in sample table " & IIf(fLocked, "is locked.", "is not locked.")

    ' See if the sample table is empty
    fEmpty = .IsTableEmpty(cstrSampleTable1)
    Debug.Print "IsTableEmpty(): The sample table " & IIf(fEmpty, "is", "is not") & " empty."

    ' Show the last error number and description
    Debug.Print "The last Jet/DAO error was " & .LastErrorDescription & " (Error #: "; .LastErrorNumber & ")"

    ' Show the path of the currently open database
    Debug.Print "The current database is stored in " & .Path

    ' Count the records in the sample table
    varValue = .RecordCount(cstrSampleTable2)
    Debug.Print "RecordCount() There are " & varValue & " records in the sample table."

    ' Show the version of the currently open database
    Debug.Print "The database version is: " & .Version

    ' All the example code is done, so we can close the database. However
    ' since we opened the database independently of the class by setting
    ' the class Database property, this call will silently fail.
    ' In other words, it won't close the database.

    .CloseDB
    Debug.Print "CloseDB(): closed the database."
  End With

  ' Close the class
  Set clsJetData = Nothing
  Debug.Print "Class terminated."

  ' Close the database that we (not the class) opened
  dbsMine.Close
  Set dbsMine = Nothing

  MsgBox "Look on the Immediate Window to see the results"

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