record locks

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13420-record-locks.html
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".
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13420
Can someone explain what my problem is, and how it can be (hopefully) fixed?

Thanks,

DLS

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13420

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DSharbaugh" wrote in message
news:38869E88-48B8-4854-A4FA-AC49276BA6EA@microsoft.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13420
> 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

Hi Allen,

Just read with interest your article on AutoCorrect. I'm using Access2000,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13420
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.

"Allen Browne" wrote in message
news:eTLSyViqGHA.4992@TK2MSFTNGP05.phx.gbl...
> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13420
>
> "DSharbaugh" wrote in message
> news:38869E88-48B8-4854-A4FA-AC49276BA6EA@microsoft.com...
>>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
>
>