|(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
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
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