PRB: PageTimeout Defaults to 5 Seconds in Jet 3.0

• Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 32-bit only, for Windows, version 4.0

SYMPTOMS

When two applications are trying to gain access to the same database using Jet 3.0, any data entered from the other user will not be updated for 5 seconds even after the code requeries the database.

STATUS

This behavior is by design. Microsoft Jet version 3.0's PageTimeout property defaults to 5 seconds. This is not the case with Jet version 2.5 that defaults to .5 seconds.

WORKAROUND

NOTE: Before running workaround, see Steps to Reproduce Behavior below.

A key needs to be added to the registry under:

   \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines

Place the following code into the General Declarations of a Form:

   Option Explicit
   Private Const HKEY_LOCAL_MACHINE = &H80000002
   Private Const REG_DWORD = 4&
   Private Const REG_OPTION_NON_VOLATILE = 0&
   Private Const KEY_ALL_ACCESS = &HF003F

   Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal lpReserved As Long, lpType As Long, lpData As Any, _
   lpcbData As Long) As Long

   Private Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long

   Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
   "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) _
   As Long

   Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias _
   "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _
   As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, _
   phkResult As Long, lpdwDisposition As Long) As Long

   Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
   ByVal cbData As Long) As Long

   Dim lpszKey As String
   Dim lpszSubKey As String
   Dim lpValueName As String
   Dim phkResult As Long
   Dim ReturnValue As Long
   Dim lresult As Long
   Dim lretval As Long
   Dim ptimeout As Long

   'Desired PageTimeout
   ptimeout = 500 'This is .5 seconds
   'Set up strings
   lpszSubKey = "Software\Microsoft\Jet\3.0\Engines\Jet"
   lpszKey = HKEY_LOCAL_MACHINE
   lpValueName = "PageTimeout"

   'If the key exists open it or if not we will create it
   lresult = RegCreateKeyEx(lpszKey, lpszSubKey, 0&, vbNullString, _
   REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, 0&, phkResult, lretval)
   'Search for lpValueName entry in lpszsubkey
   lresult = RegQueryValueEx(phkResult, lpValueName, 0, REG_DWORD, _
   lretval, 4)
   'Check return value accordingly
   Select Case lresult
      Case 0
         'Success
         'Comment next line if you don't want to change the value if exists
         lresult = RegSetValueEx(phkResult, lpValueName, 0, REG_DWORD, _
         ptimeout, 4)
      Case 2
         'If entry doesn't exist create it and set it to ptimeout
         lresult = RegSetValueEx(phkResult, lpValueName, 0, REG_DWORD, _
         ptimeout, 4)
         If lresult Then MsgBox "Could not set PageTimeout. Error:" & _
    Str$(lresult)
      Case Else
         MsgBox "Unexpected Return Value:" & Str$(lresult)
   End Select
   'Close the updated key
   lresult = RegCloseKey(phkResult)

MORE INFORMATION

Steps to Reproduce Behavior

NOTE: Perform these steps before running the workaround above.

With the Microsoft DAO 3.0 Object Library selected in Tools References:

1. Start a new project in Visual Basic. Form1 is created by default. Add a Data Control (Data1) to Form1. Bind the Data Control to a Database and set a text box to display one of the fields in the database. Add a button to do a Data1. Refresh and make an .exe.

2. Run two instances of the .exe, modify the data in one instance, and immediately click the refresh in the other instance. Notice that it takes 5 seconds for the data to be updated.