Perform data operations through DAO.
Although queries should be considered the primary mechanism for working with data in Access, there are many places where complex needs eliminate queries as an option.
|
Procedure Name
|
Type
|
Description
|
|
(Declarations)
|
Declarations
|
Declarations and private variables for the modDataOperations module
|
|
BrowseTableIndexOrder
|
Procedure
|
Process the records in a table in the order of the specified index.
The purpose of this function is to illustrate how to move through a table in index order.
|
|
CanOpenTable
|
Procedure
|
Determines if the named table can be opened by attempting to open a Recordset object on the table.
Use this function to test the availability of a table before attempting to open it. For example, the table may be attached incorrectly, or may be exclusively locked by another user.
|
|
ChangeColumnCase
|
Procedure
|
Changes the case of a field for all records in the specified table
|
|
ChangeDataSequential
|
Procedure
|
Searches for specified data in the specified field and changes it to the specified value
|
|
ChangeValueInKeyedTable
|
Procedure
|
Changes a value in a table with a primary key.
This method uses the Index property of a DAO recordset, and the Seek method to find the first match of the specified value. If a match is found, it changes the value in the specified field to the specified value. This method can only be used on recordsets of the Table type. This is because Table type recordsets are the only type that support direct access to indexes using the DAO Seek method. For information on using the Seek method on linked (attached) tables, see the SeekLinkedTable method in this module.
|
|
DataToTextFile
|
Procedure
|
Copies data from the named table or query in the current database to the named text file
This function uses the Access TransferText action to copy the contents of the named text or query to a text file. The fDelimited parameter specifies the format of the output. If this parameter is set to True, the text file is written with field delimiters. If set to False, the text file is written using Microsoft Word's Merge file format. If the file specified in the strFile parameter already exists, it is overwritten without confirmation.
|
|
DomainAverage
|
Procedure
|
Returns the arithmetic mean of a set of values in a specified set of records.
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainCount
|
Procedure
|
Returns number of records in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainFirst
|
Procedure
|
Returns the first occurrence field value in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainLast
|
Procedure
|
Returns the last occurrence field value in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainLookup
|
Procedure
|
Returns a field value in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainMax
|
Procedure
|
Returns the maximum value of a field in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainMin
|
Procedure
|
Returns the minimum value of a field in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainStDev
|
Procedure
|
Returns the standard deviation of a field in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainSum
|
Procedure
|
Returns the Sum of a field in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
DomainVar
|
Procedure
|
Returns the variance of a field in a specified set of records
This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
|
|
EmptyAllTables
|
Procedure
|
Deletes all records from all tables in the current open database. System tables (such as the MSYS... tables) are not emptied.
*** CAUTION: this deletes all data from the database! ***
Remember that space used by deleted records is not automatically reclaimed by Access. You should periodically compact the database to reclaim this space.
|
|
EmptyTable
|
Procedure
|
Deletes all records from the named table.
This procedure works by running a SQL Delete query on the specified table. Note that mass changes on table data, such as deleting all records, leaves databases in a fragmented state. Be sure to run the Jet Engine's Compact method after such changes for optimal performance.
|
|
ExportTableToText
|
Procedure
|
Exports the data from a table to a delimited text file, with one line per record.
|
|
FindDuplicates
|
Procedure
|
Creates a table with all the duplicate values in a field of a table or query.
This procedure creates a table called 'Duplicates in xxx' where xxx is the name of the table specified. This table contains one record for each duplicate value in the specified table. Use the strField parameter to specify which field is to be searched for duplicates. For example, if you have a Customers table with a field called LastName, specify the LastName field as the strField parameter to find all records with duplicate last names.
Before running this method, ensure that the output table does not already exist in the database.
|
|
FindRandom
|
Procedure
|
Returns the value of the specified field from a randomly selected record.
FindRandom works by generating a random number to match the table's record number. It does this by multiplying the number of records in the table by the random number, adding one, and then converting the resulting value to a long integer to truncate decimals. It then uses the Move method to move to that random record.
|
|
GetTableSummary
|
Procedure
|
Calculates the summary specified for a field in a table.
This procedure is similar to the Domain... procedures, but it wraps the most common functionality into one method. The following values are available for the eCalcType parameter:
Enumerated Type
--------------
Name Description
tsmctAverage return the average
tscmCount return the count
tscmFirst return the first occurrence
tscmMax return the maximum value
tscmMin return the minimum value
tscmLast return the last occurrence
tscmStDev return the standard deviation
tscmSum return the sum
tscmVar return the variance
|
|
IsRecordLocked
|
Procedure
|
Determines if the current record in the passed recordset is locked.
This procedure works by checking the recordset's LockEdits property. If True, the recordset is using Pessimistic locking which means the record is locked as soon as Edit mode is invoked. If LockEdits is False, the recordset will not be locked until the Update is issued, in which case we can't determine the locked status of a record. It then checks the EditMode property to see if an edit is in progress, which means the record is locked.
|
|
IsTableEmpty
|
Procedure
|
Determines if the named table is empty (no records) by opening a dynaset-type recordset on the specified table, and then checking EOF and BOF to determine if the table contains any records.
|
|
RankTable
|
Procedure
|
Assigns the rank of each record in a double field.
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.
|
|
RecordCount
|
Procedure
|
Returns the number of records in a table or returned by a query.
This procedure uses a SELECT COUNT query to count the total records in the specified data source. The data source can be a table or query. If a query is specified, the query must be row-returning. Note the use of the SELECT COUNT (*) SQL syntax. This form tells the engine to consider all fields in all records which allows for certain internal optimizations that make the query run fast.
|
|
SearchAllFields
|
Procedure
|
Returns the number of occurrences of the specified text in all fields of all records of the specified table or query.
This procedure opens a dynaset-type recordset on the named table or row-returning query and loops through all fields in all records searching for the specified value.
|
|
SeekLinkedTable
|
Procedure
|
Sample of using Seek/Indexes on a linked table.
Using the DAO Seek method is generally the fastest way to find a specific value in a table because it uses the tables index(s) to search on. Unfortunately, the Seek method is only available on Table-type dynasets. And since you cannot open a table-type dynaset on a linked/attached table, there is no obvious way to use the Seek method on linked/attached tables.
This procedure shows how to get around the problem. It does the following:
1. Interrogates the Connect property of the specified linked table.
2. Determines the path and name of the database where the linked table actually resides.
3. Opens a Database object on that database.
4. Opens a table-type recordset on the desired table through the newly opened database. Since the database object is open directly to the database where the linked table actually resides, the database object doesn't consider the table to be linked.
5. Sets the recordset's Index property to the name of the index to search on.
6. Uses the Seek method to find the specified value.
7. Returns a True or False value depending on whether or not the specified value was found.
Note that this procedure really doesn't do anything useful other than search for a value and illustrate the general technique. In real applications, you should modify this code to find the record(s) you want, and then perform an operation such as retrieving a field value, editing the record, etc.
|
All Our Microsoft Access Products
(885K)
