HOWTO: Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
• ActiveX Data Objects (ADO), versions 2.1 SP1, 2.1 SP2

SUMMARY

Because the Microsoft Jet database engine has a read cache and lazy writes, you can get duplicate values in your custom counter field if two applications add records in less time than it takes for the cache to refresh and the lazy-write mechanism to flush to disk. This article presents a method that takes these factors into account.

MORE INFORMATION

The Microsoft Jet database engine provides a Counter (AutoIncrement) field. Starting with Jet 4.0, you can specify a starting value and an increment value. This solves most of the need for using a custom counter that users of earlier versions of Jet had. However, if your counter is a text value or does not increase in a fixed amount, the steps outlined in this article may benefit your application.

Microsoft Jet has a read-cache that is updated every PageTimeout milliseconds (default is 5000ms = 5 seconds). It also has a lazy-write mechanism that operates on a separate thread to main processing and thus writes changes to disk asynchronously. These two mechanisms help boost performance, but in certain situations that require high concurrency, they may create problems.

The Microsoft Jet 4.0 OLEDB provider and the Microsoft Jet and Replication Objects type library provide two methods to ensure that your application has current data:

• JetEngine object's RefreshCache method.



• "Jet OLEDB:Transaction Commit Mode" Connection property.

JetEngine object's RefreshCache Method

"Jet OLEDB:Transaction Commit Mode" Connection Property

EXAMPLE

• Connection object



• Table name



• Increment value

http://support.microsoft.com/support/

Database Setup

1. In Microsoft Access, open the NWind.MDB file, and create the following tables:




           Table: KeyStore
      Field Name: NextValue
            Type: Number, Long Integer

      Field Name: Dummy
            Type: Number, Long Integer

           Table: KeyTest
      Field Name: ID
            Type: Number, Long Integer

      Field Name: Description

            Type: Text, 50
 

2. Open the KeyStore table and add a record. Set NextValue to the amount from which you want your counter to start. For example: 3.



3. Save the record and close Microsoft Access.

Test Program

1. Open a new Visual Basic or Visual Basic for Applications project with a Form (Form1) and a Module (Module1).



2. From the Project menu, choose References and add a reference to the Microsoft ActiveX Data Objects 2.1 Library and the Microsoft Jet and Replication Objects 2.1 Library.



3. Add the following code to the Module:




Option Explicit

Private Const MAX_RETRIES = 10

Function NextKeyValue(cn As ADODB.Connection, _
                      ByVal TableName As String, _
                      Optional Increment As Long = 1) As Long
Dim OldCommitMode As Long, rs As Recordset, ErrorCount As Long
Dim TempKeyValue As Long, Jet As JRO.JetEngine, InTrans As Boolean
  NextKeyValue = -1   ' returns this if the routine times out
  OldCommitMode = cn.Properties("Jet OLEDB:Transaction Commit Mode")
  cn.Properties("Jet OLEDB:Transaction Commit Mode") = 1
  cn.Properties("Jet OLEDB:Lock Delay") = 90 + Int(Rnd * 60)
  Set rs = New ADODB.Recordset
  Set Jet = New JRO.JetEngine
  On Error GoTo NKV_Error
  rs.Open TableName, cn, adOpenDynamic, adLockPessimistic, adCmdTableDirect
  cn.BeginTrans
  InTrans = True
NKV_InnerRetry:
  If rs.EditMode Then rs.CancelUpdate
  rs!Dummy = 0            ' lock the record
  Jet.RefreshCache cn     ' get current data
  TempKeyValue = rs(0)    ' .UnderlyingValue gives an error
  rs(0) = TempKeyValue + Increment
  rs.Update
  InTrans = False
  cn.CommitTrans
  rs.Close
  cn.Properties("Jet OLEDB:Transaction Commit Mode") = OldCommitMode
  NextKeyValue = TempKeyValue
  Exit Function
  
NKV_Abort:
  On Error Resume Next
  If rs.EditMode Then rs.CancelUpdate
  cn.RollbackTrans
  rs.Close
  cn.Properties("Jet OLEDB:Transaction Commit Mode") = OldCommitMode
  Exit Function

NKV_Error:
  Select Case Err.Number
    Case &H80004005
      ' Various locking errors (above)
      ErrorCount = ErrorCount + 1
      If ErrorCount < MAX_RETRIES Then
        Resume NKV_Abort
      ElseIf InTrans Then
        Resume NKV_InnerRetry
      Else
        Resume
      End If
    Case Else   ' unhandled errors
      Err.Raise Err.Number, Err.Source, Err.Description
  End Select
End Function
 

4. Add a command button (Command1) and a text box (Text1) to the form.



5. Add the following code to the form:




Option Explicit

Dim cn As ADODB.Connection

Private Sub Command1_Click()
Dim SQL As String, I As Long
  For I = 1 To 100
    SQL = "INSERT INTO KeyTest VALUES (" & _
          NextKeyValue(cn, "KeyStore", 10) & _
          ",'Test Record " & Me.hWnd & " " & Time & "')"
    On Error GoTo C1_Error
    cn.Execute SQL, , adExecuteNoRecords
    On Error GoTo 0
    Text1 = CStr(I)
    Me.Refresh
  Next I
  Exit Sub
  
C1_Error:
  Resume
End Sub

Private Sub Form_Load()
  Randomize
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\moonbase\public\northwind40.mdb"
End Sub

Private Sub Form_Unload(Cancel As Integer)
  cn.Close
End Sub
 

6. In Visual Basic, compile the application to an EXE. Run the application from multiple computers and click Command1 at the same time. It will run to completion (barring any unhandled errors) and then add records without duplicate ID numbers.

NOTES

• The KeyStore table does not require an index. You will get better performance and have fewer problems if you do not add (or allow Microsoft Access to add) and index or Primary Key to the table.



• The fewer indexes on the KeyText table, the better. Indices have a negative effect on the ability to lock and update data quickly. If you have problems in your application, try reducing the number of indices.



• Unlike Data Access Objects (DAO) or Remote Data Objects (RDO), ADO does not have an Edit method. In order to turn on pessimistic locking, the program modifies the "Dummy" field in the record to get the same effect. This field has been added to the table solely for this purpose and does not contain any useful information.
  

REFERENCES

For more information about Jet and ADO, please see:

Migrating from DAO to ADO and Using ADO with the Jet Provider

Q191253 HOWTO: Implement Multi-user Custom Counters in DAO 3.5

Additional query words: count autonumber custom multiuser kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords : kbADO kbClient kbDatabase kbJET kbOLEDB kbGrpVBDB kbDSupport
Version : WINDOWS:2.1 SP1,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto