C.3. Undocumented Tools and Resources

There are several Jet-specific utilities and object methods that are shipped with Microsoft Access 2003. These utilities and methods are either not very well documented by Microsoft or not documented at all. Notwithstanding, you can use them to help you develop and maintain your DAO applications.

C.3.1. Utilities

C.3.1.1. ISAMStats

Microsoft Jet 4.0 contains an undocumented DBEngine method called ISAMStats, which returns various internal statistics. You use ISAMStats to get statistics about different operations. For example, if you want to determine which of several queries will run faster, you can use ISAMStats to return the number of disk reads performed by each query.

Each of the ISAMStats options maintains a separate statistics counter that records the number of times its metric occurs. To reset the counter, set the Reset argument to True. The syntax is as follows:

lngReturn = DBEngine.ISAMStats(StatNum [, Reset])

Where StatNum is one of the following values:

You must call ISAMStats twice: once to get a baseline statistic and once (after the operation to be analyzed) to get the final statistic. You then subtract the baseline statistic from the final one, to arrive at the statistic for the operation under test. The following example demonstrates two ways to use ISAMStats:

C.3.1.1.1. Method 1

Call DBEngine.IsamStats(0, True)
Set rs = db.OpenRecordset("qryGetOverdueAccts", dbOpenSnapshot)
Debug.Print "Total reads: " & DBEngine.IsamStats(0)

In the above example, the first call resets the ISAMStats counter. The code
then opens a recordset using a query you want to test. The last line retakes
the statistics and prints it.
Method 2:
lngBaseline = DBEngine.IsamStats(0)
Set rs = db.OpenRecordset("qryGetOverdueAccts", dbOpenSnapshot)
lngStatistic = DBEngine.IsamStats(0)
Debug.Print "Total reads: " & lngStatistic - lngBaseline

C.3.1.1.2. Method 2

In Method 2, ISAMStats is not reset, but its return value is stored in a variable. The code then opens the recordset. The third line retakes the statistic after the operation, while the fourth and final line calculates the actual statistic.

C.3.1.2. ShowPlan

Microsoft Jet implements a cost-based query optimizer in its query engine. Jet determines the most effective way to execute a query while it's compiling it. You can view this plan using the ShowPlan Registry setting. To use ShowPlan, add the following key to the registry:

\ HKEY_LOCAL_MACHINESOFTWAREMICROSOFTJet4.0EnginesDebug

Then add a String data type entry called "JETSHOWPLAN" (in uppercase). Turn ShowPlan on or off by setting its value to "ON" or "OFF", respectively.

When the feature is on, and when you restart Access and open a database, Jet begins to log query optimization plans into a text file called SHOWPLAN.OUT, which is created in the current directory (or the My Documents folder in Windows XP). If this file already exists, subsequent query plans will be appended to the file. You need only to open a query in Design view, modify it, and save it in order to force Jet to recreate the plan for the query.

ShowPlan is completely undocumented and unsupported by Microsoft, and as such, you would be wise to treat its use with care. Some of the known issues with ShowPlan are as follows:

• Closing one database and opening another without exiting and restarting Access will result in query plans for the new database not being logged.

• Some internal Access queries will likely appear in the log file.

• Plan logging may adversely affect application performance.

• As the log file will grow over time, you should delete it (or delete its contents) periodically.

• Parameter queries and subqueries are not logged.

• ShowPlan has been known to incorrectly log information for other queries.

C.3.2. Methods

C.3.2.1. DAO.privDBEngine

The unsupported privDBEngine object allows you to connect to an external database that uses a different Workgroup Information File to the one currently being used. You can open an Access database without having to create another instance of Access. privDBEngine only allows access to DAO objects, such as TableDefs and QueryDefs, and so on.

Dim dbX As PrivDBEngine
Dim wsX As Workspace
Dim dbe As Database
'Return a reference to a new instance of the PrivDBEngine object
Set dbe = New PrivDBEngine
'Set the SystemDB property to specify the workgroup file
dbe.SystemDB = strWIFPath
`Specify the username (this could be any valid username)
dbe.DefaultUser = strUserName
`Specify the password
dbe.DefaultPassword = strPassword
`Set the workspace
Set wsX = dbe.Workspaces(0)
'Open the secured database
Set dbe = ws.OpenDatabase(strDBPath)

The PrivDBEngine object does nothing more than create a new instance of the Jet engine. You can get the same functionality by doing the following:

Dim dbe As DAO.DBEngine
Set dbe = CreateObject("DAO.DBEngine")

Note: The following table lists the CreateObject argument for different versions of the Jet engine.

C.3.2.2. Recordset.Collect

The DAO Recordset object exposes a hidden, undocumented property; Collect. Although Collect is a property, it behaves like the Recordset object's Fields collection, but it's faster because it doesn't need a reference to the Field object. Collect only returns a field's value; it doesn't expose any other properties. You can use this property by passing it a numeric item number, or a field name, just like the Fields collection. For example:

Set rs = db.OpenRecordset("tblCustomers")
Debug.Print "CustID: " & rs.Collect(0)
Debug.Print "CustomerNo: " & rs.Collect("CustomerNo")

Figure C-3 shows the comparative speed of the Collect property versus the other well-known ways of returning field values.

Figure C.3. Figure C-3

C.3.2.3. Recordset.Parent

The undocumented recordset Parent property is an object reference to the database to which the recordset belongs. This may be especially useful in situations where you have several Database objects in the same application.