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

Example code to work with table and query data in a Jet/Access database using the ActiveX Data Object (ADO) object model in VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modADOJetData module.
ADOChangeJetDataSequential Procedure Search for data in a field and change its value using a sequential search through all records. Use this function to change all occurrences or only the first occurrence. As an enhancement, you may want to add a transaction to this function to buffer disk writes for improved performance.
ADOChangeJetColumnCase Procedure Changes the case of the named field in the named Jet table using ADO. This code shows how to change table data programmatically. In this case, it would generally be faster to use an update query to accomplish case conversion on a field.
ADOChangeValueInJetKeyedTable Procedure Searches for specified data in the specified field and changes it to the specified value. This is done using the Seek method to quickly find the required record. Note that the table identified by the strTable parameter must have a primary key defined, and the primary key must consist of only one field.
ADODomainAverageJet Procedure Get the arithmetic mean of a set of values in a specified set of records. This function uses ADO to reproduce the Access DAvg (Domain Average) function.
ADODomainCountJet Procedure Get the count of a set of values in a specified set of records. This function uses ADO to reproduce the Access DCount (Domain Count) function.
ADODomainFirstJet Procedure Get the first occurrence of a specified value in a specified set of records. This function uses ADO to reproduce the Access DFirst (Domain First) function.
ADODomainLastJet Procedure Get the last occurrence of a specified value in a specified set of records. This function uses ADO to reproduce the Access DLast (Domain Last) function.
ADODomainLookupJet Procedure Get the a lookup value from the specified field. This function uses ADO to reproduce the Access DLookup (Domain Lookup) function.
ADODomainMaxJet Procedure Get the maximum value from the specified field. This function uses ADO to reproduce the Access DMax (Domain Maximum) function.
ADODomainMinJet Procedure Get the minimum value from the specified field. This function uses ADO to reproduce the Access DMin (Domain minimum) function.
ADODomainStDevJet Procedure Get the standard deviation of the specified field. This function uses ADO to reproduce the Access DStDev (Domain Standard Deviation) function.
ADODomainSumJet Procedure Get the sum of the specified field. This function uses ADO to reproduce the Access DSum (Domain Sum) function.
ADODomainVarJet Procedure Get the variance of the specified field. This function uses ADO to reproduce the Access DVar (Domain Var) function.
ADOEmptyTable Procedure Delete all records from the specified table by creating a SQL DELETE query on the fly and executing it. Remember that space used by deleted records is not automatically reclaimed by Access. You should periodically compact the database to reclaim this space.
ADOExportJetTableToText Procedure Uses ADO to export the data from a table to a delimited text file. Fields are delimited with the character specified by the strFDelimit parameter. If this parameter is left blank, semi-colons are used. Records are delimited by newline characters (Chr$(13) and Chr$(10)). This function does not exclude OLE and Memo fields.
ADOFindJetDuplicates Procedure Create a table with all the duplicate values in a field of a table or query. Note that ADODB does not support multivalue fields of ACCDB databases. If you need that, use Jet/DAO.
ADOFindJetDuplicateRecords Procedure Create a table with all the records with duplicate values in a field of a table or query. Note that ADODB does not support multivalue fields of ACCDB databases. If you need that, use Jet/DAO.
ADOFindJetDuplicateValues Procedure Create a table with just the duplicate values from a field of a table or query. Note that ADODB does not support multivalue fields of ACCDB databases. If you need that, use Jet/DAO.
ADOFindRandomJetRecord Procedure Get the value of the specified field from a randomly selected record. This procedure finds a random record by generating a random number that falls within the specified data source's number of records. It then walks through the records using the Move method until it arrives at the record specified by the random number. Since Move is a sequential read of the data, it may be slow on large tables.
ADOGetJetDataSummary Procedure Calculates the summary you specify of a field in a table. This is alternative to the built in Access domain functions.
ADOIsJetTableEmpty Procedure Determine if the named data source contains no records by opening a recordset on the table and checking the recordset's EOF and BOF properties.
ADOJetRecordCount Procedure Get the number of records in the specified data source. This is done by opening a SQL SELECT COUNT query on the table or query. Note that in multi-user environments, the number returned may not be completely accurate due to other users adding and deleting records. On large tables, this procedure may take a large amount of time to complete.
ADORankJetTable Procedure Assign the rank of each record in a double field (handles ties). Tied records are averaged. For instance, if records 3, 4, 5 and 6 are tied, their rank is 4.5.
ADOSearchAllJetFields Procedure Searches all fields in the specified data source and returns the count of occurrences of the specified search string.
Note: This technique (programmatically walking through each record of each field looking for a match) can be extremely slow. In general, use a query for better performance. Use this code when you need to incorporate specialized searching that may not be available in a query.
' 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

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