HOWTO: Implement Multi-user Custom Counters in DAO 3.5

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 32-bit only, for Windows, version 5.0

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 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. However, it starts at 1 and increments by 1. In order to do something different, such as increment by 10, you have to implement your own counter mechanism. In order to do so, you need to be aware of how some Microsoft Jet performance optimizations affects your implementation.

Microsoft Jet has a read-cache that is updated every PageTimeout milli- seconds (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 can create problems.

Earlier versions of Microsoft Jet did not expose full programmatic control over concurrency. To refresh the cache, you had to close and re-open the database. Microsoft Jet 2.x and earlier did not have a lazy-write mechanism.

The Microsoft Jet 3.5 database engine provides two methods to ensure that your application has current data:

• DBEngine.Idle dbRefreshCache



• CommitTrans dbForceOSFlush

DBEngine.Idle dbRefreshCache

CommitTrans dbForceOSFlush

Example

http://msdn.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
   
      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 that you want your counter to start from. Example: 3.



3. Save the record and closeMicrosoft Access.

Test Program

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



2. Add a reference (Project | References or Tools | References) for Microsoft DAO 3.5 Object Library.



3. Add the following code to the Module:

   
      Option Explicit
      Private Const MAX_RETRIES = 10
   
      Function NextKeyValue(db As Database, _
                            ByVal TableName As String, _
                            Optional ws As Workspace = Nothing, _
                            Optional Increment As Long = 1) As Long
      Dim rs As Recordset, ErrorCount As Long, TempKeyValue As Long
        NextKeyValue = -1   ' Returns this if the routine times out
        On Error GoTo NKV_Err
        ' Random delay between 90ms and 150ms prevents race condition
        DBEngine.SetOption dbLockDelay, 90 + Rnd * 60
        ' use default workspace if not supplied
        If ws Is Nothing Then Set ws = DBEngine(0)
        ' Error should occur on the next line if table is in use
        ' Open it exclusively
        Set rs = db.OpenRecordset(TableName, dbOpenTable, _
                                  dbDenyRead Or dbDenyWrite)
        DBEngine.Idle dbRefreshCache         ' refresh read cache
        TempKeyValue = rs(0)                 ' get value to use
        ws.BeginTrans
          rs.Edit
          rs(0) = TempKeyValue + Increment   ' value for next call
          rs.Update
        ws.CommitTrans dbForceOSFlush        ' flush the lazy-write cache
        rs.Close
   
        NextKeyValue = TempKeyValue
        Exit Function
   
      NKV_Abort:               ' clean up the mess
        On Error Resume Next
        ws.Rollback
        rs.Close
        Exit Function
   
      NKV_Err:
        Select Case Err.Number
          Case 3008, 3009, 3189, 3211, 3260, 3261, 3262
            ' various locking errors (above)
            ErrorCount = ErrorCount + 1
            If ErrorCount > MAX_RETRIES Then
              Resume NKV_Abort
            Else
               Resume
            End If
          Case Else  ' unhandled errors
            Err.Raise Err.Number, Err.Source, Err.Description
        End Select
   
      End Function
    




4. Add a CommandButton (Command1) and a Text Box (Text1) to the form.



5. Add the following code to the form:

   
         Option Explicit
         Dim ws As Workspace, db As Database
   
         Private Sub Command1_Click()
         Dim SQL As String, I As Long
           For I = 1 To 100
             SQL = "INSERT INTO KeyTest VALUES (" & _
                   NextKeyValue(db, "KeyStore", ws, 10) & _
                   ",'Test Record " & Time & "')"
             db.Execute SQL
             Text1 = CStr(I)
             Me.Refresh
           Next I
         End Sub
   
         Private Sub Form_Load()
           Randomize
           Set ws = DBEngine(0)
           Set db = ws.OpenDatabase("NWIND.MDB")
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
           db.Close
           ws.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 add records without duplicate ID numbers.

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



2. 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.

REFERENCES

For more information about Jet and DAO, please refer to your Visual Basic documentation.

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

Keywords : kbGrpVBDB
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbhowto