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?

 

[Chris O'C via AccessMonster.com]

The range of a long data type is from -2,147,483,648 to 2,147,483,647.
That's nearly 4.3 billion records. It'll fit.

Chris
Microsoft MVP

 

[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?

 

[Chris O'C via AccessMonster.com]

You're not doing anything wrong. It's just a limitation due to default
settings being set so low. It's rarely a problem, but you ran into it.

Temporarily increase your MaxLocksPerFile. Push the Ctrl+G keys to open the
immediate window and paste the following code in the window:

DBEngine.SetOption dbMaxLocksPerFile, 200000

Push the enter key to execute. Now try adding an autonumber primary key to
the table again. If it doesn't work, up the number to 300000 and try again.
I'm guessing how many locks your transaction needs, so try increasing the
number a few more times to see if Jet will let you change your table.

If you're successful, change the max locks back to a more reasonable number
when you're done with your table.

DBEngine.SetOption dbMaxLocksPerFile, 20000

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

If increasing the max locks doesn't work, copy your table (structure only, no
data). Add the autonumber primary key to the new table, then make an append
query that copies the records from the old table to the new one.

Back up your db. Turn off track name autocorrect if it's on. Copy all the
relationships from the old table to the new one. Delete the old table and
rename the new one to the same name as the old one.

Chris
Microsoft MVP

 

[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.