473,388 Members | 989 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Access 2K MaxLocksPerFile

Hello everyone, I have searched around the web and cannot find any more
information on this topic.
I'm hoping that some intelligent programmer in this group can help me out.

I have an Access 2K database with 2.5 million records in it. The database
is approximately 260Mb in size.
I was having a problem with updating the records because the MaxLocksPerFile
were set to 9500 (default). I then changed this value to 200K by editing
the registry. The database now functions correctly.

1. Will this change to my registry adversly affect my other databases?
(PS. I do know how to change the MaxLocks temporarily without editing the
registry).
2 There is no documentation (that I can find) that will explain how many
locks are needed for X number of records.
3. How can I figure out how many locks I need based on the number of
records I have? Is there an formula to do this?

Thanks to all that can help
Paul
Nov 13 '05 #1
7 8325
Paul,
I have never needed to change 'MaxLocksPeFile' other then on a Novell-network.
Is this Novell what you are talking about?
If so, I have somer network-settings that might help you.

--
Arno R
"Paul" <pz****@rogers.com> schreef in bericht news:yt********************@rogers.com...
Hello everyone, I have searched around the web and cannot find any more
information on this topic.
I'm hoping that some intelligent programmer in this group can help me out.

I have an Access 2K database with 2.5 million records in it. The database
is approximately 260Mb in size.
I was having a problem with updating the records because the MaxLocksPerFile
were set to 9500 (default). I then changed this value to 200K by editing
the registry. The database now functions correctly.

1. Will this change to my registry adversly affect my other databases?
(PS. I do know how to change the MaxLocks temporarily without editing the
registry).
2 There is no documentation (that I can find) that will explain how many
locks are needed for X number of records.
3. How can I figure out how many locks I need based on the number of
records I have? Is there an formula to do this?

Thanks to all that can help
Paul

Nov 13 '05 #2
Bri


Paul wrote:
Hello everyone, I have searched around the web and cannot find any more
information on this topic.
I'm hoping that some intelligent programmer in this group can help me out.

I have an Access 2K database with 2.5 million records in it. The database
is approximately 260Mb in size.
I was having a problem with updating the records because the MaxLocksPerFile
were set to 9500 (default). I then changed this value to 200K by editing
the registry. The database now functions correctly.

1. Will this change to my registry adversly affect my other databases?
(PS. I do know how to change the MaxLocks temporarily without editing the
registry).
2 There is no documentation (that I can find) that will explain how many
locks are needed for X number of records.
3. How can I figure out how many locks I need based on the number of
records I have? Is there an formula to do this?

Thanks to all that can help
Paul


I've only come across this when syncing a replicated db that has had
modifications that impact tens of thousands of records in the same
table. If you are running into this in a none replicated db then you
might want to mention what you are doing that is causing this to happen.

1. AFAIK, more locks use more RAM and with today's PCs you won't even
notice it.

2. I couldn't find any documentation that said anything other than 'if
you get this error change it to a larger number.'

3. It is likely one to one for records in the same transaction or
transaction nest.

HTH

--
Bri

Nov 13 '05 #3

"Arno R" <ar****************@tiscali.nl> wrote in message
news:42**********************@dreader2.news.tiscal i.nl...
Paul,
I have never needed to change 'MaxLocksPeFile' other then on a
Novell-network.
Is this Novell what you are talking about?
If so, I have somer network-settings that might help you.
No Arno,

Its not a Novell Network. However, I am writing to 2.5 million records and
updating ALL of them. This is where the MaxLock issue comes into play
--
Arno R
"Paul" <pz****@rogers.com> schreef in bericht
news:yt********************@rogers.com...
Hello everyone, I have searched around the web and cannot find any more
information on this topic.
I'm hoping that some intelligent programmer in this group can help me out.

I have an Access 2K database with 2.5 million records in it. The database
is approximately 260Mb in size.
I was having a problem with updating the records because the
MaxLocksPerFile
were set to 9500 (default). I then changed this value to 200K by editing
the registry. The database now functions correctly.

1. Will this change to my registry adversly affect my other databases?
(PS. I do know how to change the MaxLocks temporarily without editing the
registry).
2 There is no documentation (that I can find) that will explain how many
locks are needed for X number of records.
3. How can I figure out how many locks I need based on the number of
records I have? Is there an formula to do this?

Thanks to all that can help
Paul

Nov 13 '05 #4
Thanks Bri,

I am running some update code on ALL 2.5 million records in a single table
using a recordset based on a query that contains no criteria. I'm simply
using the query as my source/destination and updating ALL records in it. I
had to also put in a loop because the 7200rpm hard drive could not update
and save the records in time for the update to fire again.

Any further help would greatly be appreciated.

Thanks
Paul
"Bri" <no*@here.com> wrote in message
news:oZA3e.878300$Xk.363589@pd7tw3no...


Paul wrote:
Hello everyone, I have searched around the web and cannot find any more
information on this topic.
I'm hoping that some intelligent programmer in this group can help me
out.

I have an Access 2K database with 2.5 million records in it. The
database is approximately 260Mb in size.
I was having a problem with updating the records because the
MaxLocksPerFile were set to 9500 (default). I then changed this value to
200K by editing the registry. The database now functions correctly.

1. Will this change to my registry adversly affect my other databases?
(PS. I do know how to change the MaxLocks temporarily without editing
the registry).
2 There is no documentation (that I can find) that will explain how
many locks are needed for X number of records.
3. How can I figure out how many locks I need based on the number of
records I have? Is there an formula to do this?

Thanks to all that can help
Paul


I've only come across this when syncing a replicated db that has had
modifications that impact tens of thousands of records in the same table.
If you are running into this in a none replicated db then you might want
to mention what you are doing that is causing this to happen.

1. AFAIK, more locks use more RAM and with today's PCs you won't even
notice it.

2. I couldn't find any documentation that said anything other than 'if you
get this error change it to a larger number.'

3. It is likely one to one for records in the same transaction or
transaction nest.

HTH

--
Bri

Nov 13 '05 #5
Bri
Paul,

Is it necessary to do the update in a recordset? If you could do it in
an Update Query then a lot of your problems would go away. First of all,
it would run MUCH faster. Also, Access would be able to break the update
into several subbatches (behind the scenes) that would not exceed the
MaxLocks. If you must use a recordset (which is rarely the case) then
you should look at changing the Cursor to a ForwardOnly type (check help
on the method as you don't say if you are using DAO or ADO). This allows
the recordset to open much quicker and makes the movement to the next
record faster too. Its drawback is that you can only move forward
through the recordset and cannot go back to any other record without
reopening the recordset. You might also want to test if enclosing this
process in a transaction helps. This allows a lot of the task to be done
in RAM rather than directly on the HD. You might still hit the MaxLocks
with this, but you can change the value in the registry to get past it.
FWIW, I set my MaxLocks to 10 Million on a P2-200 Win98 laptop that has
a replicated db on it, without any issues at all.

I'm not sure what you mean by; "I had to also put in a loop because the
7200rpm hard drive could not update and save the records in time for the
update to fire again."

HTH

--
Bri

Paul wrote:
Thanks Bri,

I am running some update code on ALL 2.5 million records in a single table
using a recordset based on a query that contains no criteria. I'm simply
using the query as my source/destination and updating ALL records in it. I
had to also put in a loop because the 7200rpm hard drive could not update
and save the records in time for the update to fire again.

Any further help would greatly be appreciated.

Thanks
Paul


Nov 13 '05 #6
Thank again for your help Bri,

I will try what you said and see how it works.

BTW, the part at the bottom about the 7200rpm drive refers to the fact that
the recordset is being updated faster than the hard drive can store the
changes...so i had to slow the processing down to let the drive catch up.

Paul

"Bri" <no*@here.com> wrote in message
news:JDe4e.898009$6l.881519@pd7tw2no...
Paul,

Is it necessary to do the update in a recordset? If you could do it in an
Update Query then a lot of your problems would go away. First of all, it
would run MUCH faster. Also, Access would be able to break the update into
several subbatches (behind the scenes) that would not exceed the MaxLocks.
If you must use a recordset (which is rarely the case) then you should
look at changing the Cursor to a ForwardOnly type (check help on the
method as you don't say if you are using DAO or ADO). This allows the
recordset to open much quicker and makes the movement to the next record
faster too. Its drawback is that you can only move forward through the
recordset and cannot go back to any other record without reopening the
recordset. You might also want to test if enclosing this process in a
transaction helps. This allows a lot of the task to be done in RAM rather
than directly on the HD. You might still hit the MaxLocks with this, but
you can change the value in the registry to get past it. FWIW, I set my
MaxLocks to 10 Million on a P2-200 Win98 laptop that has a replicated db
on it, without any issues at all.

I'm not sure what you mean by; "I had to also put in a loop because the
7200rpm hard drive could not update and save the records in time for the
update to fire again."

HTH

--
Bri

Paul wrote:
Thanks Bri,

I am running some update code on ALL 2.5 million records in a single
table using a recordset based on a query that contains no criteria. I'm
simply using the query as my source/destination and updating ALL records
in it. I had to also put in a loop because the 7200rpm hard drive could
not update and save the records in time for the update to fire again.

Any further help would greatly be appreciated.

Thanks
Paul

Nov 13 '05 #7
I'm having the same problem, but I don't think I can update because I'm trying to change the datatype of a field.
Dec 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Trevor Fairchild | last post by:
I'm working with an ado recordset in VB6 - connecting to a database and updating every record for a new index number based on a filter set ordered by the user. I get the "File Sharing Lock Count...
63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
13
by: bill | last post by:
I am trying to convince a client that dotNet is preferable to an Access project (ADP/ADE). This client currently has a large, pure Access MDB solution with 30+ users, which needs to be upgraded....
1
by: Dave | last post by:
Hello NG, Regarding access-declarations and member using-declarations as used to change the access level of an inherited base member... Two things need to be considered when determining an...
13
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages...
0
by: Frederick Noronha \(FN\) | last post by:
---------- Forwarded message ---------- Solutions to Everyday User Interface and Programming Problems O'Reilly Releases "Access Cookbook, Second Edition" Sebastopol, CA--Neither reference book...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
20
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to...
10
by: WannaKatana | last post by:
I am just wondering why, with nothing else running and executing an update query against a very large table, does Access seem to be causing less than 10% processor usage. Then it says "There is not...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.