Although creating a user profile (a custom set of Microsoft Jet registry keys and settings specifically for your application) is more flexible than overwriting the Microsoft Jet default values, it still requires that you maintain those values in the registry. If you choose to change the default values of registry settings, you will have to edit the registry every time changes are required. In previous versions of Microsoft Jet, these were the only two strategies available.
Microsoft Jet 3.5 provides a new way to modify registry settings at run time with the SetOption method. This is the recommended way to fine-tune registry settings for your application. This method provides the most control over how the registry is changed. The following table lists the Microsoft Jet registry settings and the corresponding DAO constants that can be manipulated at run time.
Registry setting | DAO constant |
PageTimeout | dbPageTimeout |
SharedAsyncDelay | dbSharedAsyncDelay |
ExclusiveAsyncDelay | dbExclusiveAsyncDelay |
LockRetry | dbLockRetry |
UserCommitSync | dbUserCommitSync |
ImplicitCommitSync | dbImplicitCommitSync |
MaxBufferSize | dbMaxBufferSize |
MaxLocksPerFile | dbMaxLocksPerFile |
LockDelay | dbLockDelay |
RecycleLVs | dbRecycleLVs |
FlushTransactionTimeout | dbFlushTransactionTimeout |
The SetOption method only affects the run-time values of the registry and does not physically change the values in the registry. Thus, once Microsoft Jet is restarted or is run by any other application, it will read the original values in the registry. This means that in order to control the Microsoft Jet registry settings, you must use the SetOption method in code that runs every time an application starts. In Visual Basic, you could call the SetOption method in a Sub Main() procedure. In Microsoft Access, you could call the SetOption method in the Load event of a form that’s automatically loaded when the database opens, or you could call a function in a macro named AutoExec, which automatically runs when the database opens.
The following code samples illustrate how you might use the SetOption method to compare values for the MaxBufferSize setting. Note that these code examples use the ISAMStats function described in “Unsupported Tuning Functions” later in this chapter. The ISAMStats function returns information about the number of disk reads and writes that occur for the different registry values.
The first procedure sets the MaxBufferSize setting to 128K, then opens a recordset. It calls other procedures to reset the ISAMStats values, update the recordset, begin and commit a null transaction to prevent other asynchronous activity, and print the values returned by the ISAMStats function. The procedure then sets the MaxBufferSize setting to 2048K and repeats the process.
Sub CompareBufferSizes() Dim dbs As Database, rst As Recordset, wrk As Workspace Dim strSQL As String Dim strDbPath As String strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb" Set dbs = OpenDatabase(strDbPath) strSQL = "SELECT * FROM Customers;" ' Use SetOption to set MaxBufferSize to 128K. DBEngine.SetOption dbMaxBufferSize, 128 ' Open recordset. Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) ' Return default workspace. Set wrk = Workspaces(0) ' Set ISAMStats counters to zero. ResetISAMStats ' Run update on recordset. UpdateRecordset rst ' Commit null transaction. NullTransaction wrk ' Print ISAMStats values. PrintISAMStats strSQL ' Set MaxBufferSize to 2048K and repeat all. DBEngine.SetOption dbMaxBufferSize, 2048 Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) Set wrk = Workspaces(0) ResetISAMStats UpdateRecordset rst NullTransaction wrk PrintISAMStats strSQL End Sub ' This procedure resets the values of ISAMStats. Sub ResetISAMStats() Dim intI As Integer ' Reset each value. For intI = 0 To 5 DBEngine.ISAMStats intI, True Next End Sub ' This procedure runs an update on the recordset. Sub UpdateRecordset(rst As Recordset) Dim strCompanyName As String, strContactName As String Do Until rst.EOF rst.Edit strCompanyName = rst!CompanyName strContactName = rst!ContactName rst!CompanyName = strCompanyName rst!ContactName = strContactName rst.Update rst.MoveNext Loop End Sub ' This procedure begins and commits a null transaction. Sub NullTransaction(wrk As Workspace) ' The null transaction ensures there will be ' no more asynchronous activity that could ' yield inaccurate statistics. wrk.BeginTrans wrk.CommitTrans End Sub ' This procedure prints the values of ISAMStats ' to the Debug window. Sub PrintISAMStats(Optional strSQL As String) Debug.Print "Query: ", strSQL Debug.Print "Number of disk reads: ", DBEngine.ISAMStats(0) Debug.Print "Number of disk writes: ", DBEngine.ISAMStats(1) Debug.Print "Number of reads from cache: ", DBEngine.ISAMStats(2) Debug.Print "Number of reads from read-ahead cache: ", DBEngine.ISAMStats(3) Debug.Print "Number of locks placed: ", DBEngine.ISAMStats(4) Debug.Print "Number of release lock calls: ", DBEngine.ISAMStats(5) Debug.Print End Sub
Note When the dbMaxBufferSize constant is modified from 128 to 2048, the number of writes will decrease significantly. The low value used in this example is exaggerated to illustrate the point while using the NorthwindTables database. It is not recommended that you use a value this low for a real-world scenario.