Class: JetEngine in Category Access/Jet Databases : Database from Total Visual SourceBook

Manage Access database characteristics such as passwords, encryption, compact, workspaces and tuning through DAO for VBA and VB6.

The Microsoft Jet Database engine is exposed through the Data Access Objects (DAO) interface as the DBEngine object. Through this object, you can set various engine-wide parameters, and work with Jet workspace and database objects. This class puts a layer of high-level functionality on the Jet Engine. You can use the properties and methods in the class to perform engine-level operations, such as database maintenance, setting performance tuning options, retrieving low-level statistics, and setting workgroup options for secured databases.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CJetEngine class.
Database Property Get a handle to the current open database. This property exposes the database object in use by the class. This object only has meaning after it has either been Set to an existing database object, or a call has been made to the class OpenDBInWorkspace method.
ISAMCacheReads Property Get the number of Jet Engine cache reads since the last reset. Use the ResetISAMCacheReads method to reset this value to 0. This value returns the number of times the Jet Engine has read data from its internal cache. This number does not include the number of reads from the read-ahead cache (see the ISAMRACacheReads property for information on the read-ahead cache). Use the ResetISAMCacheReads method to reset this value to 0.
ISAMDiskReads Property Get the number of Jet Engine disk reads since the last reset. Use the ResetISAMCacheReads method to reset this value to 0. This value returns the number of times the Jet Engine has read data from its internal cache. This number does not include the number of reads from the read-ahead cache (see the ISAMRACacheReads property for information on the read-ahead cache).
ISAMDiskWrites Property Get the number of Jet Engine disk writes since the last reset. Use the ResetISAMDiskWrites method to reset this value to 0. This value returns the number of times the Jet Engine has written data to a local or network disk. This figure includes all writes, including background read-ahead cache reads that happen in separate threads. One write doesn't necessarily equal one page, as is often the case when using transactions.
ISAMLocksPlaced Property Get the number of Jet Engine locks placed since the last reset. Use the ResetISAMLocksPlaced method to reset this value to 0.
ISAMLocksReleased Property Get the number of Jet Engine locks released since the last reset. Use the ResetISAMLocksReleased method to reset this value to 0.
Note: The number of locks placed and locks released may not match. This is because a single call to release a lock may result in the release of more than one lock.
ISAMRACacheReads Property Get the number of Jet Engine read-ahead cache reads since the last reset. Use the ResetISAMRACacheReads method to reset this value to 0. This value returns the number of times the read-ahead cache has been read. In some cases, the Jet Engine reads more pages of data than are requested--this additional data is stored in the read-ahead cache so that it is readily available when needed.
TuneExclusiveAsyncDelay Property Set the Jet Engine's ExclusiveAsyncDelay tuning value, which controls the length of time to defer an asynchronous flush of an exclusive database. The default when Jet is started is 2000 milliseconds.
TuneFlushTransactionTimeout Property Set the Jet Engine's FlushTransactionTimeout tuning value. This entry disables both the ExclusiveAsyncDelay and SharedAsyncDelay settings. To enable those settings, a value of zero must be entered. FlushTransactionTimeout changes the Jet's method for doing asynchronous writes to a database file.
TuneImplicitCommitSync Property Set the Jet Engine's ImplicitCommitSync tuning value which controls whether the system waits for a commit to finish. A value of False instructs the system to proceed without waiting for the commit to finish; a value of True instructs the system to wait for the commit to finish. The default when Jet is started is False.
TuneLockDelay Property Set the Jet Engine's LockDelay tuning value. This setting works in conjunction with the LockRetry setting in that it causes each LockRetry to wait 100 milliseconds before issuing another lock request.
TuneLockRetry Property Set the Jet Engine's LockRetry tuning value which controls the number of times to repeat attempts to access a locked page before returning a lock conflict message. The default when Jet is started is 20.
TuneMaxLocksPerFile Property Set the Jet Engine's MaxLocksPerFile tuning value which prevents transactions in Jet from exceeding the specified value. If the locks in a transaction attempts to exceed this value, then the transaction is separated into two or more parts and partially committed.
TunePageTimeout Property Set the Jet Engine's PageTimeout tuning value. This controls the time between when data that is not read-locked is placed in an internal cache and when it's invalidated, expressed in milliseconds. The default when Jet is started is 5000 milliseconds.
TuneRecycleLVs Property Set the Jet Engine's RecycleLongValues tuning value. This setting, when enabled, causes Jet to recycle long value (LV) pages (Memo, Long Binary [OLE object], and Binary data types). Jet 3.0 would not recycle those types of pages until the last user closed the database. If the RecyleLVs setting is enabled, Jet 3.5 will start to recycle most LV pages when the database is expanded (that is, when groups of pages are added).
TuneSharedAsyncDelay Property Set the Jet Engine's SharedAsyncDelay tuning value which controls the length of time, in milliseconds, to defer an asynchronous flush of a shared database (not open in Exclusive mode). The default when Jet is started is 0.
TuneUserCommitSync Property The Jet Engine's UserCommitSync tuning value which controls whether the system waits for a commit to finish. A value of Yes (default value) instructs the system to wait; a value of No instructs the system to perform the commit asynchronously. By default, calls to the CommitTrans method of Jet cause all unwritten data within the transaction to be written to the disk before any other activity, i.e. synchronously. This is the recommended setting because you can be sure your transactions are committed or rolled back before other parts of your application continue. For performance reasons, there are some cases where you may to write transaction committals in asynchronous mode. In this mode control returns to your program immediately after you issue the CommitTrans method, and the Jet Engine writes pending data in a background thread. Such a setting can result in better performance, but is not generally recommended because you cannot be sure if and when the transaction is committed to disk.
WorkgroupPath Property Set the workgroup information file to use.
WorkspaceName Property Get the name of the workspace. This property must be set before any other operations if you want to use a workgroup other than the default.
CloseDBInWorkspace Method Close the current database.
CloseWorkspace Method Close the current workspace.
CreateNewDatabase Method Create a new Jet database with format, password, encryption and language options.
DatabaseCompactGeneral Method Compact the specified database. Note that Access cannot compact the current database from VBA code. Set the database's Compact on Close option to do that.
DatabaseCompact Method Compact a standard database that is not under workgroup security to a new database name. Compacting a database accomplishes several things:
  • Moves data pages for each table into adjacent locations to speed table access.
  • Discards unused space left over from object and record deletions, and resizes the database accordingly.
  • Resets the increment value for AutoNumber (counter) fields so the next number used is one more than the current highest number for each field.
  • Rebuilds the statistics for tables that are used to generate query optimization plans. All queries are flagged as uncompiled so the next time they run they will use the updated statistics. Compacting a database requires exclusive access to the database. If other users have the database open, or other processes or applications on your computer have the database open, the Compact operation will fail.
DatabaseDecrypt Method Decrypt the named database or a copy of the database. Decrypting a database reverses the act of encrypting a database. For more information on database encryption, see the EncryptDatabase method of this class, or search DAO online help for "EncryptDatabase". This operation requires exclusive access to the database. If other users have the database open, or other processes or applications on your computer have the database open, the operation will fail.
DatabaseEncrypt Method Encrypt the named database to itself or to a new database. Encryption is one of the security features available to Microsoft Jet databases. Encrypting a database makes the contents of the database file unreadable from external sources. Un-encrypted Jet databases contain viewable data that can be seen at the operating system level with tools such as hex editors. While the data is difficult to read (because of embedded high-order bits), it is nonetheless decipherable. On the other hand, encrypted databases are completely unreadable because every byte in the database file has been encrypted using the RSA RC4 algorithm with a 32-bit key for every 2K page. To encrypt a database, you must be logged in as a member of the Admins group of the workgroup information file that was in use when the database was created. Encrypted databases have a 10-15% performance degradation over unencrypted databases. Also, since encryption works by removing repeated patterns in data, an encrypted database is essentially uncompressible using popular compression utilities such as Zip, ARJ, and ARC. Similarly, encrypted databases take more space on compressed archives such as tape drives and compressed disk drives. Note that encrypting a database does not add any additional security as far as applications such as Access and Visual Basic are concerned--an encrypted database can be opened just like any other database without Jet user-level security. The only difference is that the database file cannot be read using external tools such as hex editors. For full data security, you must implement user-level Security. This operation requires exclusive access to the database. If other users have the database open, or other processes or applications on your computer have the database open, the operation will fail.
DatabasePasswordChange Method Change the password of a database or create a copy of the database with the new password.
DatabasePasswordRemove Method Remove the password for a database or create a copy of the database without a database password.
OpenDBInWorkspace Method Open the named Access/Jet database in the class's current workspace. Use this method after creating your own workspace with the CreateWorkspace method of the class. The database that you specify will be opened in the class workspace, which essentially logs you on as the user specified when the workspace was created.
OpenWorkspace Method Create a Jet workspace object. This is the mechanism you use to log into a secure workgroup/database. All subsequent calls to access database objects through the workspace will be constrained by the permission and group membership security active for the user you logged in as.
RefreshCache Method Force Jet to refresh its cache. Version 3 of the Jet Engine included an optimization that changed the way that the shared cache was kept current. Under the new scheme, databases open in shared mode are monitored to see if any activity is happening--if not, the shared cache is not refreshed. This results in significant performance gains on shared databases, approaching the levels of databases opened exclusively. The drawback of this optimization is that users on shared databases can end up waiting up to 10 seconds before seeing changes made by other users. One way to solve this problem is the set the PageTimeout registry setting (see the TunePageTimeout property in this class for details) to a lower value, but this causes performance degradation for other Jet operations. The best solution to this problem is to use the class RefreshCache method. This method calls the DAO.DBEngine.Idle method with the new (as of Jet 3.5) dbRefreshCache constant. Call this method when you want to make other user's changes visible. Don't call this method unnecessarily, or increased network and disk activity will occur.
ResetISAMCacheReads Method Reset the Jet Engine Cache Reads statistic.
ResetISAMDiskReads Method Reset the Jet Engine Disk Reads statistic. When you start the Jet Engine, it keeps various statistics updated. These ISAM statistics are continually updated and the values accumulate. Use this method to reset this specific statistic to 0. This is typically done when you are about to start an operation and you want to see the statistics for that specific operation.
ResetISAMDiskWrites Method Reset the Jet Engine Disk Writes statistic. When you start the Jet Engine, it keeps various statistics updated. These ISAM statistics are continually updated and the values accumulate. Use this method to reset this specific statistic to 0. This is typically done when you are about to start an operation and you want to see the statistics for that specific operation.
ResetISAMLocksPlaced Method Reset the Jet Engine Locks Placed statistic. When you start the Jet Engine, it keeps various statistics updated. These ISAM statistics are continually updated and the values accumulate. Use this method to reset this specific statistic to 0. This is typically done when you are about to start an operation and you want to see the statistics for that specific operation.
ResetISAMLocksReleased Method Reset the Jet Engine Locks Released statistic. When you start the Jet Engine, it keeps various statistics updated. These ISAM statistics are continually updated and the values accumulate. Use this method to reset this specific statistic to 0. This is typically done when you are about to start an operation and you want to see the statistics for that specific operation.
ResetISAMRACacheReads Method Reset the Jet Engine ReadAhead Cache Reads statistic. When you start the Jet Engine, it keeps various statistics updated. These ISAM statistics are continually updated and the values accumulate. Use this method to reset this specific statistic to 0. This is typically done when you are about to start an operation and you want to see the statistics for that specific operation.
TransactionCommit Method Commit the current transaction.
TransactionRollback Method Roll the current transaction back.
TransactionStart Method Start a transaction in the current workspace. Note that a workspace needs to be started with the OpenWorkspace method.
GetFileName Private Return the file name with extension without the leading drive and folder names.
GetFileNameNoExt Private Given a file with complete path, return just the file name without the extension.
GetPathFromFullPath Private Get the path (drive and folders) without the file name from a fully qualified file name.
' Example of CJetEngine
'
' 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_CJetEngine()
  ' Comments: Examples of using the CJetEngine class in VBA and VB6.
  '           See results in the Immediate Window.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrWorkgroupPath As String = cstrSamplePath & "WORKGRP.MDW"
  Const cstrSampleDatabase As String = cstrSamplePath & "SAMPLE.MDB"
  Const cstrTempDatabase As String = cstrSamplePath & "TMPJET.MDB"
  Const cstrSampleTable As String = "Customers"

  Dim clsJetEngine As CJetEngine
  Dim rst As DAO.Recordset
  Dim strRetVal As String
  Dim intCounter As Integer

  ' Clean up temp objects from previous runs of this example
  On Error Resume Next
  Kill cstrTempDatabase
  On Error GoTo 0

  ' Instantiate the class
  Set clsJetEngine = New CJetEngine

  ' Before initializing the class workspace objects, we must specify which Workgroup Information file to use.
  ' To make this work, we must de-instantiate the Jet Engine itself. We do this by setting DBEngine to nothing
  Set DAO.DBEngine = Nothing

  ' Reset all the statistics to read them later
  With clsJetEngine
    .ResetISAMCacheReads
    .ResetISAMDiskReads
    .ResetISAMDiskWrites
    .ResetISAMLocksPlaced
    .ResetISAMLocksReleased
    .ResetISAMRACacheReads
  End With

  ' Before doing anything else, point the Jet Engine to the appropriate workgroup information file.
  clsJetEngine.WorkgroupPath = cstrWorkgroupPath

  ' To "log into" the secured workgroup, we'll open a workspace and pass it the appropriate user name and password values.
  clsJetEngine.OpenWorkspace "MyTest", "admin", ""

  ' Show the current workspace name to verify that it worked
  Debug.Print "Our new workspace is named " & clsJetEngine.WorkspaceName

  ' Now open the sample database. Further access to this database will be allowed or disallowed according the user we logged in as.
  clsJetEngine.OpenDBInWorkspace cstrSampleDatabase, False, False

  ' Try to open the Customers table. If the user we logged in as doesn't have sufficient permission, the call will fail
  Set rst = clsJetEngine.Database.OpenRecordset(cstrSampleTable, DAO.dbOpenTable)
  Debug.Print "Test recordset has " & rst.RecordCount & " records."

  ' Show how to use transactions
  clsJetEngine.TransactionStart

  ' Add 100 records
  For intCounter = 1 To 100
    rst.AddNew
    rst![CustomerID] = "ZZ" & intCounter
    rst![CompanyName] = "Any Company"
    rst.Update
  Next intCounter

  ' Undo all the records we added by rolling back all the transactions
  clsJetEngine.TransactionRollback

  ' If you want to save the new records, use the TransactionCommit command instead of rollback:
  'clsJetEngine.TransactionCommit

  ' Close the recordset
  rst.Close

  ' Close the database we opened
  clsJetEngine.CloseDBInWorkspace

  ' Finally, close the workspace. This effectively logs us off.
  clsJetEngine.CloseWorkspace

  ' The next set of examples creates a database in the sample folder.

  ' Create the Jet 4 database
  If clsJetEngine.CreateNewDatabase(cstrTempDatabase, dbVersion40, "", False) Then
    Debug.Print "Database [" & cstrTempDatabase & "] created."
  Else
    Debug.Print "Database [" & cstrTempDatabase & "] could not be created."
  End If

  ' Compact the database using DatabaseCompact
  strRetVal = clsJetEngine.DatabaseCompact(cstrTempDatabase, "", False)
  If strRetVal = "" Then
    Debug.Print cstrTempDatabase & " compacted using DatabaseCompact."
  Else
    Debug.Print cstrTempDatabase & " could not be compacted. Error: " & strRetVal
  End If

  ' Compact the database using DatabaseCompactGeneral (which has more options than DatabaseCompact)
  strRetVal = clsJetEngine.DatabaseCompactGeneral(cstrTempDatabase, "", False, "", "", False, False, False)
  If strRetVal = "" Then
    Debug.Print cstrTempDatabase & " compacted using DatabaseCompactGeneral."
  Else
    Debug.Print cstrTempDatabase & " could not be compacted. Error: " & strRetVal
  End If

  ' Encrypt the database
  strRetVal = clsJetEngine.DatabaseEncrypt(cstrTempDatabase, "", False)
  If strRetVal = "" Then
    Debug.Print cstrTempDatabase & " encrypted."
  Else
    Debug.Print "Error encrypting " & cstrTempDatabase & ". Error: " & strRetVal
  End If

  ' Decrypt the database
  strRetVal = clsJetEngine.DatabaseDecrypt(cstrTempDatabase, "", False)
  If strRetVal = "" Then
    Debug.Print cstrTempDatabase & " decrypted."
  Else
    Debug.Print "Error decrypting " & cstrTempDatabase & ". Error: " & strRetVal
  End If

  ' Add a password to the database
  strRetVal = clsJetEngine.DatabasePasswordChange(cstrTempDatabase, "", False, "", "password")
  If strRetVal = "" Then
    Debug.Print cstrTempDatabase & " updated with a database password."
    strRetVal = clsJetEngine.DatabasePasswordRemove(cstrTempDatabase, "", False, "password")
    Debug.Print cstrTempDatabase & " database password removed."
  Else
    Debug.Print "Password could not be added to " & cstrTempDatabase
  End If

  ' Force Jet to refresh its cache (in general this method should not be called unnecessarially because it increases network activity)
  clsJetEngine.RefreshCache

  With clsJetEngine
    ' Show the engine statistics so far
    Debug.Print "ISAMCacheReads: " & .ISAMCacheReads
    Debug.Print "ISAMDiskReads: " & .ISAMDiskReads
    Debug.Print "ISAMDiskWrites: " & .ISAMDiskWrites
    Debug.Print "ISAMLocksPlaced: " & .ISAMLocksPlaced
    Debug.Print "ISAMLocksReleased: " & .ISAMLocksReleased
    Debug.Print "ISAMRACacheReads: " & .ISAMRACacheReads
  End With

  ' The following lines show how to set tuning parameters. Uncomment the lines to try them.
  With clsJetEngine
    .TuneExclusiveAsyncDelay = 100
    .TuneFlushTransactionTimeout = 300
    .TuneImplicitCommitSync = True
    .TuneLockDelay = 100
    .TuneLockRetry = 10
    .TuneMaxLocksPerFile = 10000
    .TunePageTimeout = 7000
    .TuneRecycleLVs = 1
    .TuneSharedAsyncDelay = 30
    .TuneUserCommitSync = True
  End With

  ' De-instantiate the class
  Set clsJetEngine = Nothing
  Debug.Print "Class de-instantiated."

End Sub

Total Visual SourceBook 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!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"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

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS