Module RandomID lock error VBA 2

[EBee]

When I run this program for 12k records it gave me an error of something like "lock optimistic max. . . ". Then when I looked at the table records. . it populated about 9k records with randomID and nothing on the rest. When I tried to run it again it gave me an error " Current Recordset does not support updating. This maybe a limitation of the provider, or the selected locktype". Where can I set the properties to allow updating and turn off the lock properties. I could potentially have to update about 60k records with randomID. - Thanks


Dim db As ADODB.Connection
Dim fld As Field
Dim rst As New Recordset
Dim strSQL As String
Dim strTableName As String
Dim RandomID As Double

' Place a random number in the new field for each record

Set db = CurrentProject.Connection
rst.Open "tblAllAddresses", db, adOpenKeyset
rst.MoveFirst

Do
Randomize
rst![RandomID] = Rnd() * 1000000
rst.Update
rst.MoveNext

Loop Until rst.EOF
rst.Close
Set rst = Nothing

 

[VBslammer]

Sounds like the MaxLocksPerFile error (3052). You can increase the max locks in the registry:

[tt]HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile[/tt]

It uses a DWORD key so the value is in hexadecimal, but its default value is 9500 locks. You can also set a temporary value in code that will persist until you change it again or shut down Access. If you wanted to bump the number up to allow for more locks than 9500, do this before you start the transaction:

DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, 14000

This setting was added to the DAO engine to compensate for Novell network limitations regarding record locks.

VBSlammer

"You just have to know which screws to turn." - Professor Bob