Increase Record Limit in Auto Number

[Ken Hudson]

I want to set an auto number field in a table that will contain hundreds of
thousands of records. The default structure is Long Integer and won't work.
How can I exceed that limit?

 

[Ken Hudson]

Hi Chris,
Thanks for the reply. I guess something else is going on.
I have 350,000 records in a table. I went into table design and added a
primary key field and set it to auto number. When I close the table and save
the change, I get a "File sharing lock count exceeded. Increase
MaxLocksPerFile registry entry" message. What am I doing wrong here?

 

[John W. Vinson]

I want to set an auto number field in a table that will contain hundreds of
thousands of records. The default structure is Long Integer and won't work.
How can I exceed that limit?

A Long Integer will increment to 2147483647, jump to -2147483648, and then
count up to 0. Adding one record a second, 24/7 with no holidays, you can add
records for a bit over 176 years before hitting the limit.

What makes you think it "won't work"?

 

[Ken Hudson]

Hi John,
I was wrong about the long integer. I was thinking integer. (Actually I
guess I wasn't thinking!)
When I did finally get my question correct, Chris gave me the solution.
I don't why the error occurred but running the code in the immediate window
worked.

 

[John Cramer]

I have found a workaround to this problem.
- Create a copy of your table.
- Verify that it has all records in the copy, then delete all records from the original table.
- Open the original table in design view and add the Autonumber field.
- Run an append query to append all fields from the backup table to the original table.