ACC: "Record Lock Threshold Exceeded" with Large Action Query

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

If you exceed the maximum number of locks set per connection for the Novell server, you may receive the following error message on the server:

   Record Lock threshold exceeded

If you are using Microsoft Access version 2.0 and you have not installed the Service Pack and you receive reserved error -1106, you might try installing the Service Pack before installing the Novell Patch.

For more information about the Service Pack, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack

RESOLUTION

To work around this behavior, follow these steps.

NOTE: The Novell patch mentioned in this article is not needed if you are running Netware version 3.12 or later.

1. Obtain the TTSFIX.NLM dynamic patch directly from Novell. You can find this file on the World Wide Web at http://support.novell.com/search/ff_index.htm. Enter TTSFIX.NLM in the Enter Filename textbox. \** Confirmed on 2/24/98. --Robdil

2. Set the Record Lock threshold to 10,000. Even though the server should no longer crash, Microsoft Access may still encounter problems when the server exceeds the lock count.

   There are no reports of any problems with the Record Lock threshold set to a high value. Even with this maximum set, if the threshold is reached, Microsoft Access stops; with the fix, the Novell network does not crash.

3. Load the TTSFIX.NLM dynamic patch on the server to ensure that it does not crash if a lock count is exceeded.

   NOTE: The 311LPTD.ZIP file also contains PATCHMAN.NLM and TTSFIX.NLM. We recommend that these files be loaded in the AUTOEXEC.NCF of the server. This action requires a LOAD PATCHMAN.NLM, followed by a LOAD TTSFIX.NLM in the AUTOEXEC.NCF.

                                            Range        Defaults
                                            ---------------------------
#1 SET Maximum record locks per connection: 10-10,000    default 500
#2 SET Maximum file locks per connection:   10-1,000     default 250
#3 SET Maximum record locks:                100-200,000  default 20,000
#4 SET Maximum file locks:                  100-100,000  default 10,000

NOTE: This behavior is related to the record locks setting on the Novell server. This behavior is not related to the MS-DOS SHARE.EXE locks setting.

MORE INFORMATION

NOTE: Microsoft Access 97 has two new features that may be helpful when working with large action queries: MaxLocksPerFile is a new Jet 3.5 registry setting and UseTransaction is a new query property. Microsoft Access will split action queries that need more locks than specified by MaxLocksPerFile into multiple transactions. As long as the value for MaxLocksPerFile, default value of 9500, is smaller than the record locks per connection on your Netware server, you won't receive the above error.

For more information about MaxLocksPerFile, search for "SetOption," and then "SetOption Method" using the Microsoft Access 97 Help Index. To see the description of MaxLocksPerFile, click "Initializing the Microsoft Jet 3.5 Database Engine" jump at the bottom of the SetOption Method help topic.

The products discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.