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

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

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.

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?

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

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.

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

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

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.

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

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.

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. :p

sportguy

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. :confused: 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:

DoCmd.OpenQuery "Make My Annoying Table", acViewNormal

Maybe I should be following those up with something like:

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):
rst.LockEdits = False