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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)