Module: ADOJetData in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

' Example of modADOJetData
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modADOJetData()
  ' Comments: Examples of using the modADOJetData module to work with table and query data in a Jet/Access database using the ActiveX Data Object (ADO) object model in VBA and VB6.
  '           See the Immediate Window for results.

  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrSampleDB As String = cstrSamplePath & "SAMPLE.MDB"
  Const cstrTempFile As String = "modADOJetData.txt"
  Const cstrOrdersTbl As String = "Orders"

  ' Temporary tables
  Const cstrTempTbl As String = "tempCategories"
  Const cstrTempDuplicateOrdersTbl As String = "tempDuplicateOrders"
  Const cstrTempDuplicateCustomersTbl As String = "tempDuplicateCustomers"

  ' Access Jet provider constants
  Const cstrJetProvider351 As String = "Microsoft.Jet.OLEDB.3.51"
  Const cstrJetProvider4 As String = "Microsoft.Jet.OLEDB.4.0"
  Const cstrJetProvider12 As String = "Microsoft.ACE.OLEDB.12.0"
  Const cstrJetProvider14 As String = "Microsoft.ACE.OLEDB.14.0"

  Dim cnn As ADODB.Connection
  Dim strProvider As String

  ' Use ACE provider to support the ACCDB version of Access.
  #If VBA7 Then
    strProvider = cstrJetProvider12
  #Else
    strProvider = cstrJetProvider4
  #End If

  Set cnn = New ADODB.Connection

  ' Open the connection
  With cnn
    .CursorLocation = adUseServer
    .Open "Provider=" & strProvider & ";Data Source=" & cstrSampleDB
  End With

  ' Example of ADOChangeJetColumnCase
  ADOChangeJetColumnCase cnn, "Products", "ProductName", vbProperCase

  ' Example of ADOChangeJetDataSequential
  ADOChangeJetDataSequential cnn, "Products", "UnitPrice", 18#, "UnitPrice", 18.01, False

  ' Example of ADOChangeValueInJetKeyedTable
  ADOChangeValueInJetKeyedTable cnn, "Products", "PrimaryKey", 2, "UnitPrice", 18.99

  ' Example of ADODomainAverageJet
  Debug.Print "Average: " & ADODomainAverageJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainCountJet
  Debug.Print "Count  : " & ADODomainCountJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainFirstJet
  Debug.Print "First  : " & ADODomainFirstJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainLastJet
  Debug.Print "Last   : " & ADODomainLastJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainLookupJet
  Debug.Print "Lookup : " & ADODomainLookupJet(cnn, "Products", "UnitPrice", "ProductID=2")

  ' Example of ADODomainMaxJet
  Debug.Print "Maximum: " & ADODomainMaxJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainMinJet
  Debug.Print "Minimum: " & ADODomainMinJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainStDevJet
  Debug.Print "StdDev : " & ADODomainStDevJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainSumJet
  Debug.Print "Sum    : " & ADODomainSumJet(cnn, "Products", "UnitPrice", "")

  ' Example of ADODomainVarJet
  Debug.Print "Var    : " & ADODomainVarJet(cnn, "Products", "UnitPrice", "")

  ' Delete previously created temp tables
  On Error Resume Next
  cnn.Execute "DROP table " & cstrTempTbl
  cnn.Execute "DROP table " & cstrTempDuplicateOrdersTbl
  cnn.Execute "DROP table " & cstrTempDuplicateCustomersTbl
  On Error GoTo 0

  ' Create tempCategories table with just two fields
  cnn.Execute "SELECT CategoryName, Description " & _
              "INTO tempcategories FROM Categories"

  ' Search for the number of fields with "and" in it (inexact match)
  Debug.Print "Inexact Matches for 'and': " & ADOSearchAllJetFields(cnn, "and", cstrTempTbl, False)

  ' Search for the number of fields equal to "Cheeses" (exact match)
  Debug.Print "Inexact Matches for 'and': " & ADOSearchAllJetFields(cnn, "Cheeses", cstrTempTbl, True)

  ' Export data to text file
  Debug.Print "Exported records to " & cstrTempFile & ": " & ADOExportJetTableToText(cnn, cstrTempTbl, cstrTempFile, "|")

  ' Get a random record's CategoryName value from a table
  Debug.Print "Random Record: " & ADOFindRandomJetRecord(cnn, cstrTempTbl, "CategoryName")

  ' Find the number of duplicates in a field
  Debug.Print "Duplicate CustomerIDs in " & cstrOrdersTbl & " : " & ADOFindJetDuplicates(cnn, cstrOrdersTbl, "CustomerID")

  ' Create a new table with a field containing the duplicate values in the CustomerID field
  Debug.Print "Duplicate records in new table " & cstrTempDuplicateOrdersTbl & ": " & ADOFindJetDuplicateValues(cnn, cstrOrdersTbl, "CustomerID", cstrTempDuplicateCustomersTbl)

  ' Create a new table with the records that have duplicate values in the CustomerID field
  Debug.Print "Duplicate records in new table " & cstrTempDuplicateOrdersTbl & ": " & ADOFindJetDuplicateRecords(cnn, cstrOrdersTbl, "CustomerID", cstrTempDuplicateOrdersTbl)

  ' Empty a table and get the number of records that were deleted
  Debug.Print "Emptying " & cstrTempTbl & " deleted records: " & ADOEmptyTable(cnn, cstrTempTbl)

  ' Example of ADOGetJetDataSummary
  Debug.Print ADOGetJetDataSummary(cnn, "Products", "UnitPrice", "SUM")

  ' Determine if a table is empty
  Debug.Print "Is Table Empty? " & ADOIsJetTableEmpty(cnn, "Products")

  ' Example of ADORankJetTable
  'ADORankJetTable cnn, "Products", "UnitPrice", "RankField", True

  ' Get the record count for a table
  Debug.Print "Number of records: " & ADOJetRecordCount(cnn, "Products")

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)