Access 2K MaxLocksPerFile

[Paul]

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

[Arno R]

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

[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

[Paul]

"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

[Paul]

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

[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

[Paul]

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

[Actuary]

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.