ACCESS

I have an ACCESS FILE with a table of about 75k records including a lot of fields with some text fields.

I am now trying to add a field with datatype autonumber. However, I am getting this error.

lock count exceeded. Increase MaxLocksPerFile registry entry.

how can I best accomplish adding my autonumber datatype field to this table.  I don't want to go through regedit
lux satTechnical ConsultantAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Copy the table schema to create a new, empty table.  
In design view, add the autonumber column to the empty table.
Create an append query to select the data from the original table and append to the new table.
Sort the query if you want the rows to be in a specific order initially.

Verify the new table.
Delete the old table
Rename the new table.
0
 
chaauCommented:
On the Microsoft website the have options that do not involve modifying the registry. There are a few samples provided, but all of them come down to this simple command:
' Set MaxLocksPerFile.
   DBEngine.SetOption dbMaxLocksPerFile, 200000

Open in new window

0
 
James ElliottManaging DirectorCommented:
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I am now trying to add a field with datatype autonumber. However, I am getting this error.
How are you doing this? Are you moving into Design view, and then trying to add it? Or are you trying to do this via code?
0
 
lux satTechnical ConsultantAuthor Commented:
Scott I am adding the new field in Design View and then trying to add it.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see ... then try running the code snippet from chaau, and the do the add again. You can run that in the Immediate window in the VBA Editor.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The other way to accomplish this is to set the "Use Transactions" property in the query to no.  That will avoid exceeding the lock limit.

Jim.
0
 
Nick67Commented:
@PatHartman has the right of it.

Adding autonumber fields to tables with existing data is--as you've seen--problematic.
It fails more often than it succeeds.

A simple copy | Paste...Structure Only, add the autonumber field and an append or copy-and-paste of the records is the easiest way to get the job done right.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.