Posted on 2014-12-11
Medium Priority
Last Modified: 2014-12-25
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
Question by:lux sat
LVL 25

Expert Comment

ID: 40495483
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

LVL 12

Expert Comment

by:James Elliott
ID: 40495553
LVL 86
ID: 40495874
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?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

by:lux sat
ID: 40495915
Scott I am adding the new field in Design View and then trying to add it.
LVL 86
ID: 40495941
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.
LVL 41

Accepted Solution

PatHartman earned 2000 total points
ID: 40496178
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.
LVL 59
ID: 40496189
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.

LVL 26

Expert Comment

ID: 40497403
@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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question