synchronize and dbMaxLocksPerFile

[Rachael]

Hi All,

Have been using db.synchronize for a month or so now with a client and no probs 'til now when the client got the file locking message, which I think I've narrowed down to the client having entered heaps of data to a replica then tried to sync and the whole thing went pear-shaped because there was too much hence the MaxLocksPerFile message.

I tried adding prior to the db.synchronize line

DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000

then this after the db.synchronize

DAO.DBEngine.SetOption dbMaxLocksPerFile, 9500

this got rid of the file locking message but now the client after about 10 minutes gets "Unregonised database format"

It seems to sync some but not all data

I initially thought it was a office 2007 service pack 1 problem ( I had it installed on one of my machines and not the new laptop which is faster and nicer), so I stopped using the nice new lappy and thought it would get rid of the Unrecogised message but to no avail.

Hope I'm makimg sense, it's gettin' late here in Oz, thank you

Kind regards,

rachael

 

[dfenton]

Here's the code I use for direct synchronization:

Public Function DirectSynchDAO(strLocal As String, strRemote As String, _
    Optional strULS As Object) As String
On Error GoTo errHandler
  Dim db As DAO.Database
  Dim lngOldMaxLocks As Long
    
  DoCmd.Hourglass True
  Set db = DBEngine.OpenDatabase(strLocal)
retrySynch:
  db.SYNCHRONIZE strRemote
  Call CheckForConflicts(db, strULS)
  
exitRoutine:
  If lngOldMaxLocks <> 0 Then
     ' reset MaxLocksPerFile to original value
     DBEngine.SetOption dbMaxLocksPerFile, lngOldMaxLocks
  End If
  If Not (db Is Nothing) Then
     db.Close
     Set db = Nothing
  End If
  DoCmd.Hourglass False
  Exit Function

errHandler:
  'MsgBox Err.Number & ": " & Err.Description, vbCritical, _
      "Error in DirectSynchDAO()"
  Select Case Err.Number
    Case 3052 
      'File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.
      ' assign old MaxLocksPerFile value to lngOldMaxLocks
      Call adh_accRegGetVal(adhcHKEY_LOCAL_MACHINE, _
           "Software\Microsoft\Jet\4.0\Engines\Jet 4.0", "MaxLocksPerFile",  _
           lngOldMaxLocks, 4096)
      DBEngine.SetOption dbMaxLocksPerFile, 100000
      Resume retrySynch
    Case Else
      DirectSynchDAO = Err.Number & ": " & Err.Description
      Resume exitRoutine
  End Select
End Function

Some points:
- strULS is a user-defined type that has the username and password, but not really relevant here, as it's used only for my subroutine that checks for conflictsl
- I use the Access Developers Handbook registry code to get the original value for MaxLocks so that I can keep track of it and set it back. This is just in case a particular machine it runs on has it set to something other than the default 9500 (I've never encountered one, but this code is designed to run everywhere without causing problems).

Now, all that really isn't any different from what you've already done, just fancier.

I would ask if you have a reliable connection to the back end. Is this being run over WiFi? If so, well, forget it! You're never going to be able to reliably run Access or a direct synch over a WiFi connection. Wired is required (10Mbps or higher), and wireless is just way, way too unreliable to ever do anything that opens a remote MDB across the wire (which is what a direct synch does).

If that's the case (or it's a WAN connection), then you *must* switch from direct to indirect synch.