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

ID: Q102522

The information in this article applies to:

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

This error can also cause a Netware 3.1x server to stop responding (crash) if it doesn't have the TTS fix installed.

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:

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

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 text box.

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.

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.

The following table details possible lock settings:


                                            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

#1 is the setting that should be increased from the default to 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.

Additional query words: netware 4.1

Keywords : kb3rdparty kberrmsg GnlMu
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbprb