Example code to work with table and query data in a Jet/Access database using the ActiveX Data Object (ADO) object model
|
Procedure Name
|
Type
|
Description
|
|
(Declarations)
|
Declarations
|
Declarations and private variables for the modADOJetData module
|
|
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.
|
|
ADOChangeJetDataSequential
|
Procedure
|
Searches for specified data in the specified field and changes it to the specified value. This is done using a sequential search through all records.
You can use this function to change all occurrences or only the first occurrence. The algorithm used is to do a sequential search through all records and changing values on records that match the search criteria. As an enhancement, you may want to add a transaction to this function to buffer disk writes for improved performance.
|
|
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
|
Returns 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
|
Returns 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
|
Returns 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
|
Returns 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
|
Returns the a lookup value from the specified field.
This function uses ADO to reproduce the Access DLookup (Domain Lookup) function.
|
|
ADODomainMaxJet
|
Procedure
|
Returns the maximum value from the specified field.
This function uses ADO to reproduce the Access DMax (Domain Max) function.
|
|
ADODomainMinJet
|
Procedure
|
Returns the minimum value from the specified field.
This function uses ADO to reproduce the Access Dmin (Domain min) function.
|
|
ADODomainStDevJet
|
Procedure
|
Returns the standard deviation of the specified field.
This function uses ADO to reproduce the Access DStDev (Domain Standard Deviation) function.
|
|
ADODomainSumJet
|
Procedure
|
Returns the sum of the specified field.
This function uses ADO to reproduce the Access DSum (Domain Sum) function.
|
|
ADODomainVarJet
|
Procedure
|
Returns the variance of the specified field.
This function uses ADO to reproduce the Access DVar (Domain Var) function.
|
|
ADOEmptyTable
|
Procedure
|
Deletes 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
|
Creates 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.
|
|
ADOFindRandomJetRecord
|
Procedure
|
Returns the value of the specified field from a randomly selected record.
This procedure find 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 MoveNext method until it arrives at the record specified by the random number. Since the MoveNext is a sequential read of the data, it can be slow on large tables. This procedure is best used on data sources with a reasonably small number of records.
|
|
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
|
Determines 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
|
Returns 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
|
Assigns 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.
The table identified by strTable must have an existing Double field that this procedure writes to. The strTable parameter specifies the table containing the data you want to rank. The strSortField parameter identifies the existing field in strTable that contains the value to rank. The strRankField parameter specifies the name of an existing Double field in the table that this procedure should write the ranking value. The fAscending parameter is set to True to rank in ascending order on strSortField, or set to False for descending sort.
|
|
ADOSearchAllJetFields
|
Procedure
|
Searches all fields in the specified data source and returns the count of occurences of the specified search string.
Note that 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.
|
All Our Microsoft Access Products
(885K)
