[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Access table ID overflow issue?

I have an Access temp table to load the data that is going to be bound the form. So there is frequent operations like inserting data into this table; delete all the records in this table, etc.

The ID filed is the primary key field, and its properties are: Long Integer/Increment/Indexed:YES(no duplicates)

My question,  this ID keeps increasing and might have overflow issue in the future. How couild we reset the ID field after every use? Any better design or fix for this kind of problem? Thanks.
0
heyday2004
Asked:
heyday2004
5 Solutions
 
Kelvin SparksCommented:
Is this table an Access table or by any chance in SQL Server. Indeed, where are the pernament tables. For temp tables like this (especially if holding one or two records at a time whether you need such a primary key. If you are retrieving data for edit there, then the permanent ID could well ne needed (and again if being used to select records for deletion.
 
Kelvin
0
 
rajvjaCommented:
Hi

 LongInteger will accept long value. I think it will be ok.
If your table grows with specific amount of size, i mean millions of records, access is not good solution for your database. Try with Sql server or Oracle bla bla
0
 
Kelvin SparksCommented:
A long integer can go up to 2,147,483,647.
 
Being a temp table you caould always delete it and recreate it when close to that limit (or even have code to delete it and recreate it every time the database opens!
Kelvin
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The ID filed is the primary key field, and its properties are: Long Integer/Increment/Indexed:YES(no duplicates)>>
 Even with the size issue, your not going to run out of numbers because an Autonumber field will wrap over to negative numbers when it hits the positive limit.  So the range of an autonumber is -2,147,483,648 to 2,147,483,647
 Basically that means that your never going to run out of autonumbers, because a JET (or even a SQL table for that matter), simply can't hold that many records.
 What it does mean however is that you should not use any type of logic that relies on an autonumber being larger (i.e. determining the last record entered in a table).
 The last is a moot point I think though as I've never heard of any application when starting at 1 that has even hit the positive limit of 2,147,483,647; that's a pretty big number.
JimD.
0
 
heyday2004Author Commented:
Thanks for the replies. It's just an Access temp table, not SQL Server table, etc. Is it ok for me to rely on this unique ID to locate the records (display records on the forms, etc.), etc. in my design logic?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Was this comment helpful? Yes No heyday2004: Thanks for the replies. It's just an Access temp table, not SQL Server table, etc. Is it ok for me to rely on this unique ID to locate the records (display records on the forms, etc.), etc. in my design logic? >>
  Yes.  Just don't make the assuption though that a newer record will always have a higher autonumber.
JimD.
 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now