Solved

ACCESS

Posted on 2014-12-11
10
134 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 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 58
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 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