When you use VB6 to develop database applications, you have several choices for local database access:
|(Declarations)||Declarations||Declarations and private variables for the CJetData class.|
|Database||Property||Get the DAO Database object of the currently opened database which you can use to get and set database properties. Don'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||Get 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||Get 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||Get the path of the open database, without the file name or extension.
Note: You can use the Name property of a DAO.Database object to retrieve the full path, name and extension of a database.
|Version||Property||Get the version of Jet used to the create 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: 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||Change 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||Search for data in a field and change its value. This method changes data values in a non-keyed table by opening a
recordset on the data source and searching through the records for a match. When a match is found, the specified fields is changed to the
Note: 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||Close the current open database.
Note: 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||Get 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||Get 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||Get 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||Get 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||Get 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||Get 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||Get 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||Get 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||Get 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||Get 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||Delete all records from the named table by running a SQL Delete query on the specified table.
Note: 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||Method||Export 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.|
|ExportTableMultiValueToText||Method||Export the data from a table to a delimited text file including support for multivalue fields in ACCDBs.|
|FindDuplicates||Method||Create 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||Get 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.|
|GetTableSummary||Method||Calculate 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.|
|IsRecordLocked||Method||Determine 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||Determine 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||Open 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||Assign 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||Get 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||Get 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:
|GetPathFromFullPath||Private||Get 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.|
' Example of the CJetData Class ' ' To use this example: ' 1. Create a new form ' 2. Create a command button called cmdTest ' 3. Paste the entire contents of this module into the new form's module ' ' This example assumes that the sample files are located in the folder named by the following constant. Private Const mcstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\" Private Sub cmdTest_Click() Const cstrSampleDatabase As String = mcstrSamplePath & "SAMPLE.MDB" Const cstrSampleTable1 As String = "Customers" Const cstrSampleTable2 As String = "Orders" Const cstrChangeTable1 As String = "DAOSample1" Const cstrOutputFile As String = mcstrSamplePath & "DAOTEST.TXT" Dim clsJetData As CJetData Dim dbsMine As DAO.Database Dim rstTest As DAO.Recordset Dim lngRecords As Long Dim fLocked As Boolean Dim fEmpty As Boolean Dim varValue As Variant Dim intChanges As Integer ' Clean up any temp files from a previous run of this example On Error Resume Next Kill cstrOutputFile On Error GoTo 0 ' Instantiate the class Set clsJetData = New CJetData Debug.Print "CJetData class instantiated." With clsJetData ' Open a database through the class If .OpenDB(cstrSampleDatabase, False, False) Then Debug.Print "OpenDB(): sample database opened." ' Browse the Customers Table in the order of its PrimaryKey Index .BrowseTableIndexOrder cstrSampleTable1, "PrimaryKey" Debug.Print "BrowseTableIndexOrder(): browsed recordset." ' Change the ContactName field in the sample table to upper case intChanges = .ChangeColumnCase(cstrChangeTable1, "ContactName", True) Debug.Print "ChangeColumnCase: changed case for " & intChanges & " records." ' Find each occurrence of 'Mexico' in the Country field in the sample table, change the Contact Title to Marketing Manager intChanges = .ChangeDataSequential(cstrChangeTable1, "Country", "Mexico", "ContactTitle", "Marketing Manager", False) Debug.Print "ChangeDataSequential(): made " & intChanges & " changes in the sample table." ' Find the record in the sample table whose PrimaryKey value is ' ANTON, and change the value of the Contact Title field to: Marketing Manager If .ChangeValueInKeyedTable(cstrChangeTable1, "ANTON", "ContactTitle", "Marketing Manager") Then Debug.Print "ChangeValueInKeyedTable(): made changes in sample table." Else Debug.Print "ChangeValueInKeyedTable(): failed to change sample table." End If ' Use the database property to get the name of the current open database. ' This shows how to use a property of the class database object that is not exposed directly by the class. Debug.Print "The current database is: " & .Database.name ' Get the average value of the Freight field for records where the OrderDate is before January 1, 1995. varValue = .DomainAverage("Freight", cstrSampleTable2, "OrderDate<#01/01/1995#") Debug.Print "DomainAverage() returned " & varValue ' Get the count of records from the OrderDate field varValue = .DomainCount("OrderDate", cstrSampleTable2, "") Debug.Print "OrderDate() retunred: " & varValue ' Get the value of the Ship Via field for the first record where the order date is before January 1, 1995. varValue = .DomainFirst("ShipVia", cstrSampleTable2, "OrderDate<#01/01/1995#") Debug.Print "DomainFirst() returned: " & varValue ' Get the value of the Ship Via field for the last record where the order date is before January 1, 1995. varValue = .DomainLast("ShipVia", cstrSampleTable2, "OrderDate<#01/01/1995#") Debug.Print "DomainLast() returned: " & varValue ' Find the record with order id of 10546 and return the value for the CustomerID field. varValue = .DomainLookup("CustomerID", cstrSampleTable2, "OrderID=10546") Debug.Print "DomainLookup() returned: " & varValue ' Get the maximum of the Freight field where the Employee ID is 4. varValue = .DomainMax("Freight", cstrSampleTable2, "EmployeeID=4") Debug.Print "DomainMax() returned: " & varValue ' Get the minimum of the Freight field where the Employee ID is 4. varValue = .DomainMin("Freight", cstrSampleTable2, "EmployeeID=4") Debug.Print "DomainMin()returned: " & varValue ' Get the standard deviation of the Freight field where the Employee ID is 4. varValue = .DomainStDev("Freight", "Orders", "EmployeeID=4") Debug.Print "DomainStDev() returned: " & varValue ' Get the Sum of the Freight field where the Employee ID is 4. varValue = .DomainSum("Freight", "Orders", "EmployeeID=4") Debug.Print "DomainSum() returned: " & varValue ' Get the variance of the Freight field from the Orders table where the Employee ID is 4. varValue = .DomainVar("Freight", "Orders", "EmployeeID=4") Debug.Print "DomainVar() returned: " & varValue ' Now we will close the class's database that we opened with the call to the OpenDB method. .CloseDB Debug.Print "CloseDB(): database closed." Else Debug.Print "OpenDB(): sample database could not be opened." End If ' The class is now in a state where it is not pointing to any database ' so any subsequent calls to methods or properties will fail. ' Next, we'll open a database and pass that database to the class. ' This is the technique to use with the class when your application already has a database open, ' and needs to pass the database to the class. Set dbsMine = DBEngine.OpenDatabase(cstrSampleDatabase) Debug.Print "Opened a database on our own." ' Now that the database is open, let's tell the class to use it by ' Seting the class Database property to the database we opened Set .Database = dbsMine Debug.Print "Sent our database to the class." ' All subsequent calls to the class will happen through the dbsMine database. ' Empty the Orders table. If .EmptyTable(cstrChangeTable1) Then Debug.Print "EmptyTable(): Delete query run on sample table." End If ' Export the records in a sample table to a text file using a semicolon as the field delimiter. lngRecords = .ExportTableToText(cstrSampleTable1, cstrOutputFile, ";") Debug.Print "ExportTableToText(): " & lngRecords & " records from table exported to file." ' Find duplicates in the sample table based on the Contact Title field. ' This example creates a table called Duplicate Customers. If this table ' already exists in the sample database, an error is returned. varValue = .FindDuplicates(cstrSampleTable1, "Duplicate Customers", "ContactTitle") Debug.Print "FindDuplicates(): duplicates for table stored in " & varValue ' Pick a random Contact Title from the sample table varValue = .FindRandom(cstrSampleTable1, "ContactTitle") Debug.Print "FindRandom(): returned: " & varValue ' Get the average of order dates using the GetTableSummary method. varValue = .GetTableSummary(tsctAverage, cstrSampleTable2, "OrderDate") Debug.Print "GetTableSummary() returned: " & varValue ' Open a recordset and lock a record. ' Then call the IsRecordLocked method to test to see if it is locked. Set rstTest = .Database.OpenRecordset(cstrSampleTable1) With rstTest .MoveFirst .LockEdits = True .Edit ' Record is now locked End With fLocked = .IsRecordLocked(rstTest) rstTest.Close Debug.Print "IsRecordLocked(): first record in sample table " & IIf(fLocked, "is locked.", "is not locked.") ' See if the sample table is empty fEmpty = .IsTableEmpty(cstrSampleTable1) Debug.Print "IsTableEmpty(): The sample table " & IIf(fEmpty, "is", "is not") & " empty." ' Show the last error number and description Debug.Print "The last Jet/DAO error was " & .LastErrorDescription & " (Error #: "; .LastErrorNumber & ")" ' Show the path of the currently open database Debug.Print "The current database is stored in " & .Path ' Count the records in the sample table varValue = .RecordCount(cstrSampleTable2) Debug.Print "RecordCount() There are " & varValue & " records in the sample table." ' Show the version of the currently open database Debug.Print "The database version is: " & .Version ' All the example code is done, so we can close the database. However ' since we opened the database independently of the class by setting ' the class Database property, this call will silently fail. ' In other words, it won't close the database. .CloseDB Debug.Print "CloseDB(): closed the database." End With ' Close the class Set clsJetData = Nothing Debug.Print "Class terminated." ' Close the database that we (not the class) opened dbsMine.Close Set dbsMine = Nothing MsgBox "Look on the Immediate Window to see the results" 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