Using the SetOption Method to Control Registry Settings

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.

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.