If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Increase Record Limit in Auto Number
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 |
#2
|
|||
|
|||
Increase Record Limit in Auto Number
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 wrote: 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? -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Increase Record Limit in Auto Number
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? -- Ken Hudson "Chris O'C via AccessMonster.com" wrote: 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 wrote: 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? -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Increase Record Limit in Auto Number
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 Ken Hudson wrote: 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? -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Increase Record Limit in Auto Number
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 Chris O'C wrote: 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 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#6
|
|||
|
|||
Increase Record Limit in Auto Number
On Mon, 30 Jun 2008 13:45:02 -0700, Ken Hudson
wrote: 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"? -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Increase Record Limit in Auto Number
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. -- Ken Hudson "John W. Vinson" wrote: On Mon, 30 Jun 2008 13:45:02 -0700, Ken Hudson wrote: 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"? -- John W. Vinson [MVP] |
#8
|
|||
|
|||
File Sharing Lock Count Exceeded
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. |
Thread Tools | |
Display Modes | |
|
|