Improve Db Performance in a Network Environ

Installing the following code into the first form that opens in a database will increase the database performance in a network environment, especially with Windows 7.

Option Compare Database
 
Public Function PrintISAMStats()
    ‘ Print the values returned by the ISAMStats function
    ‘ to the Debug window.
   
    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 Function
 
Public Function ResetISAMStats()
    ‘ This procedure resets the values returned by the
    ‘ ISAMStats function to zero.
   
    Dim intI As Integer
    ‘ Reset each value.
    For intI = 0 To 5
        DBEngine.ISAMStats intI, True
    Next
End Function
 
Public Function UpdateISAM()
    DBEngine.SetOption dbMaxBufferSize, 50000
    DBEngine.SetOption dbMaxLocksPerFile, 500000
    ‘DBEngine.SetOption dbFlushTransactionTimeout, 5000
   
End Function
 
Public Function runtest()
‘UpdateISAM
ResetISAMStats
Dim curtime As Date
curtime = Now
 
DoCmd.OpenForm “frmprc10claims”
PrintISAMStats
 
Debug.Print “Number of seconds:” & DateDiff(“s”, curtime, Now())
 
 
End Function

This entry was posted in Access 2007 problems/solutions, Uncategorized and tagged , , . Bookmark the permalink.