Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ACCESS

Posted on 2014-12-11
10
Medium Priority
?
139 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
8 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 85
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 85
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 40

Accepted Solution

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

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

581 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