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

' 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

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)