record locks

[DSharbaugh]

I am working with an inherited table (of nearly 900,000 records), which was
originally developed with a unique key. When I try to add an autonumber
field to this table in order to have a primary key field, I get the error
"maximum number of record locks exceeded". There are only about 20 data
fields and the default record locking option for the db is set at "no locks".
Can someone explain what my problem is, and how it can be (hopefully) fixed?

Thanks,

DLS

 

[Allen Browne]

This should work around whatever is causing the issue:

1. Close Access.

2. Make sure there is no LDB file in the same folder as the MDB (spurious
locking info, or indicating someone else has the file open.)

3. Open the file exclusively.
Version dependent, but in A2003:
File | Open, select file, drop-down the Open button, and choose
Exclusive.

4. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

6. Compact the database:
Tools | Database Utilities | Compact/Repair.

7. Make a copy of the table structure:
Select the table in the Database Window.
Copy (Ctrl+C).
Paste (Ctrl_V).
Choose Structure Only.

8. Open the new, empty table in design view, and add the AutoNumber field.

9. Populate the new table with an Append query.
(Apppend on Query menu, in query design.)

10. After verifying the data is correct, remove any relations on the old
table.

11. Delete the old table.

12. Compact again.

13. Rename the new table with the old name.

14. Recreate any relations you deleted at step 10.

 

[ChrisM]

Hi Allen,

Just read with interest your article on AutoCorrect. I'm using Access2000,
and was unaware of this feature.
I have built a fairly large database, that, by default, has this turned on.
I have been experiencing some of the problems you mentioned, but have
generally managed to fix them without realising what has caused them.
May try what you recommend, and import all my objects into a new 'fixed' DB.

Thanks for that.

Cheers,

Chris.