Solved

ACCESS

Posted on 2014-12-11
10
133 Views
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
0
Comment
Question by:lux sat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 25

Expert Comment

by:chaau
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

0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40495553
0
 
LVL 84
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?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

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

Accepted Solution

by:
PatHartman earned 500 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.
0
 
LVL 57
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.

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
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.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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