ACC: "File Sharing Lock Count" Error During Synchronization

• Microsoft Access 97

SYMPTOMS

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, you should first make a backup copy of the registry files (System.dat and User.dat). Both are hidden files in the Windows folder.

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you synchronize two replicas, you may receive the following error message:

   Synchronization between '<Replica1.mdb>' and '<Replica2.mdb>' failed.

   File sharing lock count exceeded (Error 3052).

CAUSE

A member of the replica set contains too many changes for synchronization to be completed successfully. For example, if one of the replicas has increased in size by 100 megabytes (MB) because you have added a large number of new records, you may receive this error. The replica that contains too many changes may be the database from which you are attempting to synchronize, or it may also be another member of the replica set.

RESOLUTION

If you are using Microsoft Access 97, you can take advantage of a new feature that allows you to increase the MaxLocksPerFile setting for Microsoft Jet database engine 3.5 by editing the Windows Registry. The default value of this setting is 9500. However, changing this value is not recommended if one of the replicas is located on a Novell Netware server because the server's maximum record locks per connection is 10000. In addition, changing this value will result in poor performance if one of the replicas is located on a server running Windows NT version 3.51 or earlier. If both replicas are located on servers running Microsoft Windows NT version 4.0 or Microsoft Windows 95, the maximum value of this setting is limited only by available resources.

WARNING: Modifications made to the system registry are considered advanced usage of the Windows 95 or Windows NT operating systems. Extreme care should be taken when making modifications. Any invalid or incorrect entries may render the operating system unusable.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows 95. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the Changing Keys And Values Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

1. Quit Microsoft Access.

2. Click the Start button, and then click Run.

3. In the Open box, type "regedit" (without the quotation marks).

4. Locate the HKEY_LOCAL_MACHINE registry folder.

5. Expand the HKEY_LOCAL_MACHINE folder until you locate the folder SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5.

6. Double-click the Jet 3.5 folder to view its contents.

7. Select the MaxLocksPerFile entry; on the Edit menu, click Modify.

8. In the Edit DWORD Value dialog box, select Decimal and increase the number in the Value Data box.

9. Click OK to save the MaxLocksPerFile setting.

10. Quit the Registry Editor.

11. Start Microsoft Access and synchronize the replicas.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The MaxLocksPerFile setting allows the Microsoft Jet database engine to complete large transactions without exceeding a specified record lock limit. Instead of exceeding this limit, the Jet database engine splits a transaction into two or more parts; after one part has been committed, the Jet database engine frees the locks so that they can be reused. As a result, fewer locks are required to complete the transaction. This prevents a Novell Netware server, which limits the maximum record locks per connection to 10000, from crashing when that value is exceeded.

However, the Jet database engine does not split the transaction when it synchronizes two replicas. If the transaction cannot be completed within the limit specified in the MaxLocksPerFile setting unless it is split into parts, you receive the error described in the "Symptoms" section; the synchronization does not occur.

Steps to Reproduce Problem

WARNING: The following steps may require up to 400 MB free disk space. The sample procedure fills a table with a large amount of data; running it may take a long period of time, during which your CPU would be unavailable.

1. Start Microsoft Access and open a new blank database.

2. Create the following table:

   Table: tblTable1 --------------------------- Field Name: ID Data Type: Number Field Size: Long Integer Field Name: Field1 Data Type: Text Field Size: 50 Field Name: Field2 Date Type: Text Field Size: 50 Field Name: Field3 Date Type: Text Field Size: 50 Field Name: Field4 Date Type: Text Field Size: 50

3. Save the table as tblTable1. When asked if you want to create a primary key, click No.

4. On the Tools menu, point to Replication, and click Create Replica. When prompted to close the database, click Yes. When prompted to make a backup, click No.

5. In the Location Of New Replica dialog box, note the location and file name of the new replica. Click OK.

6. Click OK when prompted to reopen the database, which is now the Design Master.

7. Open a new, blank module and type the following procedure:

   Function Filler(lngNumRecs As Long)

             Dim lngLoopCount As Long
             Dim strFillerString As String
             Dim db As Database
             Dim rs As Recordset
   
             strFillerString = ""
             For lngLoopCount = 1 To 40
                strFillerString = strFillerString & "x"
             Next lngLoopCount
             Set db = CurrentDb
             Set rs = db.OpenRecordset("tblTable1")
             For lngLoopCount = 1 To lngNumRecs
                rs.AddNew
                rs![ID] = lngLoopCount
                rs![Field1] = strFillerString
                rs![Field2] = strFillerString
                rs![Field3] = strFillerString
                rs![Field4] = strFillerString
                rs.Update
             Next lngLoopCount
             db.Close
          End Function
   
   

8. Type the following line in the Debug window:

   ?Filler(500000)

   Note that this procedure could take a long time to run and may require as much as 400 MB of free disk space.

9. On the Tools menu, point to Replication, and then click Synchronize Now.

10. In the Synchronize Database dialog box, click OK.

REFERENCES

For more information about the MaxLocksPerFile setting, search the Help Index for "SetOption method" and display the topic "SetOption Method (DAO)"; scroll to the bottom of the topic and click the link "Initializing the Microsoft Jet 3.5 Database Engine."

For more information about replication, please refer to the Microsoft Jet 3.5 Replication White Paper. See the following article in the Microsoft Knowledge Base for instructions about obtaining the Microsoft Jet 3.5 Replication White Paper:

   ARTICLE-ID: Q164553
   TITLE     : ACC97: Jet 3.5 Replication White Paper Available on MSL