File Sharing Lock Count Exceeded (1 Viewer)

TKnight · Jul 27, 2005

Hi,

I have some code which updates an invoice table with transaction numbers. It worked ok until this month when we had to run three months worth of invoices in one go. I recieved a runtime error 3052 "File Sharing Lock Count Exceeded" and Access told me to increase the MaxLocksPerFile registry entry. I thought i'd better check it out a bit first though and found the following on support.microsoft.com

In Microsoft Access 2000, you can take advantage of a feature that allows you to increase the MaxLocksPerFile setting for Microsoft Jet database engine 4.0 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.

The invoice table had about 20k lines but as I am on a netware server i'm not gonna be able to squeeze more than 10k lines out of my recordset so I was wondering if there is any way I can open it without record locking? The table that is being modified is never edited apart from in this one process so there is no risk of complications there.

Here is how I opened the recordset

Code:

Set rstInvs = dbs.OpenRecordset("SELECT [InvNumber], [InvNumExt], [TransactionType], [LineNum] FROM [tblInvoices]" _
& "WHERE [LineNum] Is Null ORDER BY [InvNumber], [InvNumExt], [TransactionType]")

Any help would be much appreciated.

thanks, Tom

The_Doc_Man · Jul 27, 2005

I am always suspicious of any "Max xxxxx exceeded" messages because all too often they derive from a simple problem: Failing to close what you opened.

In your code sample, you are opening a recordset. Is there a chance that your code will open that same recordset again a few times before your program exits? Like, about 999 more times?

If so, your problem is that you didn't clean up after yourself properly. Always, always, ALWAYS close what you open.

Pat Hartman · Jul 27, 2005

This is a problem with old Novel servers and no you can't avoid record locking. Since one month doesn't seem to be a problem, can't you just run one month at a time?

TKnight · Jul 28, 2005

Thanks Pat/Doc. Of course I can run one month at a time but I bet you didn't build up your vast knowledge by early submission to these things

I can also split the data into smaller recordsets if needs be.

For anyone else out there who comes up with the same thing my netware is v4.9 (SP2)

Thanks again,

Tom

The_Doc_Man · Jul 28, 2005

The other possibility that comes to mind is the nature of the update.

I don't know what you are doing, but is it possible to split the problem a different way?

What I had in mind is that you have a few fields in your recordset and you are obviously going to update the records selected based on "[LineNum] Is Null"

So can you build a table that contains the stuff you wanted to update separately as a TEMPORARY table? Then apply a single update query with some (admittedly ugly) WHERE conditions to make the updates apply to the appropriate records?

An explicit UPDATE query works a LOT differently than a multiple-open recordset operation in VBA. It MIGHT work. No guarantees, though.

Pat Hartman · Jul 28, 2005

You are correct, I don't go down without a fight but old technology is difficult to get around. I would talk to Novel and search their FAQs for the solution because the problem is not with Access, it is with Novel.

AnneEHB · Aug 30, 2005

I am having a similar problem with a newly "up graded" (97 to 2003) database.

I have a table of circa 27,000 records that is updated with some VBA code. Every record is updated in the process.

This works perfectly in the old version of the database but causes locking problems in the new one. An added complication is the upgrade from Win NT4 to XP.

Is it a problem with Windows or Access?

cheers

TKnight · Aug 31, 2005

There is a windows registry entry MaxLocksPerFile which allows you to customise the amount of records you can lock at any one time in a recordset. I think it defaults to 9500 but can be boosted depending on your machine spec. (not sure if it would go to 27000 though!)

There is an extra twist in that Novell adds it's own ceiling to the entry at 10,000 so if you are on a Novell network (particularly an ageing one) then you cannot exceed that amount.

As far as i'm aware Access will accommodate anything that Windows will so if you can boost the registry setting to that amount you should be ok. If not is there any way that you can split the processing into 3 or 4 smaller recordsets?

HTH

Tom

AnneEHB · Aug 31, 2005

Yes I have done a code work around to break up the number of updates at a time so that it now works.

But what I don't understand is that the old version in 97 running on Win NT4 has the same registry setting of 9500 as the new version in 2003 running on Win XP. Both machines are plugged into the same network.

Unfortunately I am in the process of upgrading approx 1000 databases and I really hope I'm not going to have this problem with too many of them.

TKnight · Aug 31, 2005

Yeah that is strange!

I came across this recently and it wasn't too serious so I investigated it a bit and then worked around it. The info I posted to you previously I found on support.microsoft.com (search for the registry entry).

If you do come across it loads then it may be worth investigating further to see if there is an easier way.

If you do look into it more it'd be good if you posted anything you find back here.

Good Luck!

Tom

LowTechThinking · Sep 25, 2005

Easy was to bypass MaxLocksPerFile

Run this first to get the maximum locks per file. -->
Debug.Print .AbsolutePosition 'similar to RowID
You will get the error again but write down the last number <mine was 9518>
Then insert this code after the beginning of the <Do Until .EOF> -->
If .AbsolutePosition = 9517 Then
MsgBox "It will clear the MaxLocksPerFIle"
End If
I am not sure how this thing works but i know it works on my code. It was an experimentation. Vivre la 70's.

sportsguy · Mar 2, 2006

MAxRecordsLock - another workaround

My answer to MaxRecordLocks is that the larger the number of records in combination with the number of indexes, the fewer number of lines you can add programmatically.

The alternative is to load your database unIndexed, and then create the index after you have loaded all your records. . . Access2003 can index a new file fairly quicky with a SATA drive and a Pentium 8xx or 9xx series. . .

This suggestion is from experience, as I have been loading between 100K and 1M records at a shot, and with indexes, I got to the point that i couldn't get past adding 50K records before hitting the MaxRecordLocks. At that point, the database had 2M records, and i can't load at 10K records at a time. . .

This advice is free, you are assured of getting your money's worth.

sportguy

SleepyJay · Aug 28, 2006

File Sharing Lock Pain!

Sigh. I think you all might be /slightly/ wrong or this afternoon is "No FileSharingLock hour".

I have a table that always fires this error during a weekly update. The table that always gets stuck is part of a few that get refreshed through an ODBC process. What's special about this table is that I have a script that changes some of the data in the table (creates "grouped sequences" for later use with a crosstab) after refreshing it.

I usualy just click "debug" and then run again. Usually after doing this once or twice, it finishes fine (as far as I can tell). I finally have some time to take a look at fixing it today.

As expected, when I ran the refresh this morning, it happened again. So, I tried to recreate it in an isolated environment, using some of the suggestions written in this post. I usually have a table of about 19,000 rows. For my test, I used a table with an indexed field of around 97,000 rows. And, I keep failing to get the error each time I try it.

All that indexing seemed to do was make it 10x faster! Therefore, I'm skeptical that the problem is related to indexing or the number of rows.

I'm left with maybe the ODBC somehow isn't closing properly--although I'm not calling any explicit opens. All of the refreshes are Make Tables that use ODBC-links as tables (so called "Microsoft Jet-connected ODBC data sources"). So, I have a collection of Subs that get run one after the other and contain code like:

Code:

DoCmd.OpenQuery "Make My Annoying Table", acViewNormal

Maybe I should be following those up with something like:

Code:

DoCmd.Close acQuery, "Make My Annoying Table"

Which makes no sense, and will probably throw an error of it's own.

I'm at a loss as what to do next. Any suggestions would be appreciated!

thanks
jason.

ps. I was so hoping this line was going to solve all of my problems (of course it does nothing at all in this case):

Code:

rst.LockEdits = False

ted.martin · Jun 23, 2013

Just had a similar 3052 error problem. As you can see from the code, I am updating one field in the rsR recordset following a FindFirst lookup in the rsS recordset. It was over 10,000 records and is creating the error around record 10036. Note: The updating rsR!TestSpecificationID is NOT indexed but the rsS!TestID is at it is the Primary Key on the rsS table..

I solved the problem but running the first 10,000 records and then the remainder.

If anyone has any suggestions as to preventing the 3052 error other than breaking the module to run 10,000 record at a time, then I would welcome their input.

Here is my code that errored:

Code:

 Public Function SpecTestID()

Dim db As DAO.Database
Dim rsS As DAO.Recordset
Dim rsR As DAO.Recordset
Set db = CurrentDb
Set rsS = db.OpenRecordset("Test Specifications", dbOpenSnapshot)
Set rsR = db.OpenRecordset("Test Results", dbOpenDynaset)

With rsR
.MoveFirst

Do Until .EOF

rsS.FindFirst ("RecipeID = '" & !RecipeID & "' AND TestName = '" & !TestName & "'")

If rsS.NoMatch = True Then
'    MsgBox "No Match with Recipe " & !RecipeID & " AND TestName " & !TestName, vbCritical, "No Natch"
    GoTo myNext
End If

.Edit
!SpecificationTestID = rsS!TestID
.Update

myNext:
.MoveNext
Loop

End With

Set rsS = Nothing
Set rsR = Nothing
Set db = Nothing


MsgBox "Done"

End Function

ted.martin · Jun 23, 2013

Hey; this stops it

Code:

With rsR
.movelast
DAO.DBEngine.SetOption dbMaxLocksPerFile, .recordcount

.movefirst

Pat Hartman · Jun 23, 2013

Doing an update like this with a VBA loop is EXTREMELY inefficient. An update query can join the two tables and update the column when a match is found. The query will run in seconds and I suspect that unless you have only a few hundred rows, you code loop runs much longer.

Since you are operating on tables and tables are not sorted, you are reading one table sequentially and reading the second table from the begining each time in order to find a match so who knows how many times you are reading the second table from the beginning.

There are occassions where you might need to do this with a VBA code loop because you can't create an update query to do the job. If you find that case in the future, use queries rather than tables. That way you can sort the recordsets so they are in the same sequence and code what we lovingly call a "two file match". This technique requires reading both queries from front to back ONCE. The FindFirst you are currently using (required because the recordset is not ordered), makes the database engine go back to the first record in set B for every record in set A. So if you have 1000 rows in set A and 1000 rows in set B, you are reading set B from the beginning to the middle (on average) 1000 times!!!! so that is 1000 * 500 records you have to pass through. The two file match requires you to only read set B once so instead of 500,000 records, you read 1000. Obviously, the bigger your recordsets are, the more time consuming inefficient processes become.

ted.martin · Jun 24, 2013

Thanks for a very comprehensive and informed response. I follow your thinking exactly and have learned from your comments.

It is often too simplistic to just right a bit of code to update one table from another when the design structure changes. That is what I did without thinking of the efficiency of the process. Thank you; I won't make this same mistake again.

ted.martin · Jun 24, 2013

Here is the Query in SQL form; it runs in seconds!

Code:

UPDATE [Test Results] INNER JOIN [Test Specifications] ON ([Test Results].TestName = [Test Specifications].TestName) AND ([Test Results].RecipeID = [Test Specifications].RecipeID) SET [Test Results].SpecificationTestID = [Test Specifications].[TestID]
WHERE (((Len([Test Results].[TestName] & ""))>0));

Pat Hartman · Jun 24, 2013

Strangely, this type of mistake is made more frequently by experienced programmers because they know how to code and they naturally gravitate to that solution. I'm sure I've written a million lines of code over the course of my career and that's plenty. I don't need the practice

The thing that attracted me to Access over 20 years ago was its ability to do so much with very little code. My solutions these days start with queries, then go to property settings, and finally - code. I still end up with lots of code but it is never my first solution.

File Sharing Lock Count Exceeded (1 Viewer)

Registered User.

Immoderate Moderator

Super Moderator

Registered User.

Immoderate Moderator

Super Moderator

AnneEHB

Guest

Registered User.

AnneEHB

Guest

Registered User.

LowTechThinking

Guest

Finance wiz, Access hack

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread