Properties and methods for working with data stored in Jet/Access databases using DAO.
When you use VB to develop database applications, you have several choices for local database access:
1. Use the Data Control to access Jet/Access databases.
2. Write DAO code to work against Jet/Access databases.
3. Use DAO/Jet with linked tables for heterogeneous data access to disparate data sources such as FoxPro, xBASE and Excel.
The functionality in this class enhances method #2, the use of DAO and Jet in program code to work with data.
|
Procedure Name
|
Type
|
Description
|
|
(Declarations)
|
Declarations
|
Declarations and private variables for the CJetData class
|
|
Database
|
Property
|
Gets a handle to the current open database.
The Database property represents the DAO Database object of the database the class currently has open. This allows you to:
1. Use the Database property to gain access to any of the properties and methods available for the database object that refers to the currently open database.
For example, you can instantiate the class, call the OpenDB() method, and then use the class Database property to refer to any
of the properties or methods that are applicable to the DAO Database object type.
In the code below, we use this technique to display the version of database that the class has open by using the DAO Version property of the database object:
Dim MyDB As CJetData
Set MyDB = New CJetData
MyDB.OpenDB "C:\NWIND.MDB, False, False"
Debug.Print MyDB.Database.Version
2. If your code already has the database open, you can Set the class Database property to your database variable.
In such a case, this is preferable to using the class OpenDB() method because only one instance of the database is open.
Dim dbs As DAO.Database
Dim MyDB As CJetData
Set dbs = DAO.DBEngine(0).OpenDatabase("C:\NWIND.MDB")
Set MyDB = New CJetData
Set MyDB.Database = dbs
You shouldn 't close the database object through the Database property using the DAO close method because class will no longer have the necessary state to perform operations. Instead, perform the CloseDB method if you had the class open the database, or close your own database variable that was passed to the class.
|
|
LastErrorDescription
|
Property
|
Gets a description of the last error that occurred (this is the same as the first member in the DAO.DBEngine.Errors collection)
This value is useful while debugging to determine why an operation failed.
This value is the same as the first member in the DAO.DBEngine.Errors collection.
|
|
LastErrorNumber
|
Property
|
Gets the error number of the last error that occurred (the same as the Number property of first member in the DAO.DBEngine.Errors collection.
This value is useful while debugging to determine why an operation failed.
This value is the same as the Number property of first member in the DAO.DBEngine.Errors collection.
|
|
Path
|
Property
|
Gets the path of the open database, without the file name or extension.
Note that you can use the Name property of a DAO.Database object to retrieve the full path, name and extension of a database.
|
|
Version
|
Property
|
Gets the version of Jet used to the cretae the database.
When Jet is called on to create a database, it creates a property in the new database called Version.
This property stores the version of the Jet engine that was used to create the database.
For example, if you create a database with Visual Basic version 3, or Access version 2, version 2 of the Jet Engine is used, and the Version property is 2.
Note that Jet versions 2 and 3 allow you to create databases compatible with previous versions.
For example, you can use Jet 3 to create a Jet 1.x database. In such a case, the Version property returns 1.x, since the database is considered a 1.x database.
|
|
BrowseTableIndexOrder
|
Method
|
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.
|
|
ChangeColumnCase
|
Method
|
Changes the case of a field for all records in the specified table by modifying the records individually
A more efficient alternative would be to use an Update Query, but this example is useful if you want to perform more complicated tasks for each record.
|
|
ChangeDataSequential
|
Method
|
Searches for specified data in the specified field and changes it to the specified value.
This method illustrates the technique for changing data values in a non-keyed table.
The method opens a recordset on the specified data source and walks through each record looking for a match.
When a match is found, the specified fields is changed to the specified value.
Note that the method explicitly opens a Dynaset type recordset to ensure the FindFirst and FindNext methods are available.
|
|
ChangeValueInKeyedTable
|
Method
|
Example of using the SEEK command to locate and change 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 class.
|
|
CloseDB
|
Method
|
Closes the current open database.
Note that this only works if the class opened the database through the OpenDB() method. If the class has a database open that was passed to it through the Database property, or the OpenDB() method has not yet been called, this method silently fails.
|
|
DomainAverage
|
Method
|
Returns the arithmetic mean of a set of values in a specified set of records.
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainCount
|
Method
|
returns number of records in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainFirst
|
Method
|
Returns the first occurrence field value in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainLast
|
Method
|
Returns the last occurrence field value in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainLookup
|
Method
|
Returns a field value in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainMax
|
Method
|
Returns the maximum value of a field in a specified set of records.
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainMin
|
Method
|
Returns the minimum value of a field in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainStDev
|
Method
|
Returns the standard deviation of a field in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainSum
|
Method
|
Returns the Sum of a field in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
DomainVar
|
Method
|
Returns the variance of a field in a specified set of records
This procedure is functionally identical to the intrinsic Microsoft Access function of the same name.
|
|
EmptyTable
|
Method
|
Deletes all records from the named table 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.
|
|
ExportTableMultiValueToText
|
Method
|
Exports the data from a table to a delimited text file including support for multivalue fields in ACCDBs
|
|
ExportTableToText
|
Method
|
Exports the data from a table to a delimited text file.
The data is output to an ASCII text file with one line per record.
Note that This procedure does not export complex field types such as attachment or multivalue fields.
Use the modules modAttachmentFields and modMultiValueFields to manage those field types.
|
|
FindDuplicates
|
Method
|
Creates a table with all the duplicate values in a field of a table or query
Note that this does not apply to multivalue complex fields in ACCDBs.
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
|
Method
|
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 go to that random record.
|
|
GetPathFromFullPath
|
Private
|
Returns the path without the filename from a fully qualified path.
This is a class-private help function that supports the class's methods and properties.
|
|
GetTableSummary
|
Method
|
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
-----------------------------------
tsctAverage return the average
tsctCount return the count
tsctFirst return the first occurrence
tsctMax return the maximum value
tsctMin return the minimum value
tsctLast return the last occurrence
tsctStDev return the standard deviation
tsctSum return the sum
tsctVar return the variance
|
|
IsRecordLocked
|
Method
|
Determines if the current record in the passed recordset is locked 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
|
Method
|
Determines if the named table is empty (no records) by opening a dynaset-type recordset on the specified table.
It then checks EOF and BOF properties to determine if the table contains any records.
|
|
OpenDB
|
Method
|
Opens the named Access/Jet database with the options specified.
Use this method before calling any of the class's database specific routines.
You can also point the class to a database by opening a database and setting this class's Database property to the opened database.
|
|
RankTable
|
Method
|
Assigns the rank of each record into the specified double field. Tied records are averaged.
For instance, if records 3, 4, 5 and 6 are tied, their rank is 4.5.
|
|
RecordCount
|
Method
|
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
|
Method
|
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
|
Method
|
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. See CZipCode_DAO for an example of this.
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)
