How to solve MaxLocksPerFile problem without changing registry settings?

[surotkin]

Hi all!

My code is causing a violation against the MaxLocksPerFile in the registry.
I checked Microsoft Knowledge Base http://support.microsoft.com/default.aspx?scid=kb;EN-US;815281.
It suggests increasing MaxLocksPerFile setting in registry by using

DAO.DBEngine.SetOption dbmaxlocksperfile, 1500

or manually.

So far I discovered already that the problem is caused by the following code

dim dbs as database
dim rst as recordset
set dbs = currentdb
set rst = dbs.openrecordset ("A NORMAL SELECT-QUERY")

do until rst.EOF
rst.edit
.....
rst.UPDATE
rst.MoveNext
loop

rst.close
dbs.close

I don’t mind to use SetOption method.
However, I don’t understand what does the above mentioned code locks.
In other words, what code should I use in order not to lock anything?

=> apparently the records remain locked after the edit-statement. I didn't have this problem when using Access 97. It seems that Access2002 has much more features for client-server structures but I'll have to learn how to use them. Therefore I would like to know as what type and what options I have to give to my recordset in order to achieve that each record is un-locked directly after the rst.UPDATE statement. In the help-files I didn't find an answer that solved my problem...

Any help appreciated.
Thanks.
surotkin

 

[RuralGuy]

When you go to Tools>Options>Advanced Tab how do you have Default open mode and Default record locking set?

 

[surotkin]

Hi RuralGuy,
I have the following settings:

Default open mode: Shared
Default record locking: No locks

Thanks.
surotkin

 

[RuralGuy]

Hi surotkin,
The suggested change for dbmaxlocksperfile is to 15,000 and not 1500. The default on my machine is 9500. You could also try opening the table exclusive rather than shared for this procedure, if that is an option for you. If you are not using ADO then I would also make sure that ActiveX was *not* checked in the references, otherwise disambiguate your Dim's.