UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adding/deleting Autonumber Vs Coded Incrementing Number Field, Access 2013    
 
   
JVanKirk
post Jan 11 2017, 08:11 AM
Post#1


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


I have a fairly dynamic database. Each week an import happens and in the overall process, last weeks data gets moved to an identical table(I know, not normalized entirely but it's just to keep it for a week in case it is needed to restore), the current table is purged of all records and then populated with the new data. I want to include an ID field to uniquely identify each record and was thinking about an Autonumber field but at 3000+ records per week, that number is going to get very large without resetting to 0 each time. SO, do I delete and add the Autonumber column or do I create a permanent column and just loop all the records after the import is done and increment the field value by 1. Seems the autonumber would be much quicker. I could delete the column at the beginning of the import and then add it at the end avoiding a time consuming loop...

Thoughts concerns?

TIA

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
Jeff B.
post Jan 11 2017, 08:17 AM
Post#2


UtterAccess VIP
Posts: 9,670
Joined: 30-April 10
From: Pacific NorthWet


As an alternative, what about not moving records at all, just using a query to only 'see' the older ones? Keep everything in the table it starts in, use queries to display only what you want in the form(s).

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
JVanKirk
post Jan 11 2017, 08:23 AM
Post#3


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


I had thought about that originally, including an import data to identify what date each record was brought in and then deleting the older records when the newest came in, so there would only ever be just two weeks of data in the table. But that still wouldn't resolve the issue of the autonumber, in fact, a coded incrementing number field would be to only option then. As well, every filter, query criteria, etc, would need to include a match for date, and there are a lot. It was far easier(code wise) and I think will be faster(performance wise) to dump the old data into a "holding" table for the one week. Really, as long as the original Excel files are maintained, they could reimport the old data and make it the "current" anytime they wanted to see a certain weeks stats. But this is really meant to be a current week snapshot of what manning looks like... I created the dump to the temp table as a precursor to them possibly wanting to see current and previous data side by side or to doa quick restore to the previous if needed...

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
projecttoday
post Jan 11 2017, 08:26 AM
Post#4


UtterAccess VIP
Posts: 7,966
Joined: 10-February 04
From: South Charleston, WV


Why do you want to add this field?

If you compact after deleting the records it will reset.

At the rate of 3,000 per week it will take over 13,000 years for the autonumber to max out.

--------------------
Robert Crouser
Go to the top of the page
 
JVanKirk
post Jan 11 2017, 08:27 AM
Post#5


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


Yeah I was wondering what the max was...and if this was even an issue...

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
JVanKirk
post Jan 11 2017, 08:49 AM
Post#6


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


OK, from the design/develop standpoint I actually see a reason to reset this. If I am working with someone else on the development team and I want them to check a specific record for something (in the backend), it's way easier to say go look at record 5 or even 3325 rather than 48,156...or in a couple years 1,234,567....I know this field has no value to the typical end user, but within the team, it would be way easier to have smaller numbers to refer to...

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
JVanKirk
post Jan 11 2017, 08:56 AM
Post#7


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


And..I'm also wondering what, if any, would be the negative side effects to adding and deleting the column.

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
JVanKirk
post Jan 11 2017, 09:20 AM
Post#8


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


Found some awesome code here at UA to just compact the BE after I purge the records..resets to 1!! Awesome.

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 
nvogel
post Jan 11 2017, 11:47 AM
Post#9



Posts: 718
Joined: 26-January 14
From: London, UK


You can easily reset the autonumber column anytime without compacting the database. Just do this:

ALTER TABLE YourTable ALTER COLUMN id AUTOINCREMENT(1,1);
Go to the top of the page
 
John Vinson
post Jan 11 2017, 02:20 PM
Post#10


UtterAccess VIP
Posts: 4,065
Joined: 6-January 07
From: Parma, Idaho, US


The other (possibly big) advantage to compacting is that the space occupied by the deleted records is NOT recovered or overwritten - the .accdb file will keep growing by the size of the import every week, and not shrinking. If the records are large you could start pushing the 2GByte size limit - and if that limit is exceeded it's time to look for your backup file!

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
JVanKirk
post Jan 12 2017, 08:20 AM
Post#11


UtterAccess VIP
Posts: 4,160
Joined: 4-March 04
From: Clarksville, TN


John, great point and really that and avoiding corruption is why I went the route of compacting to reset the autonumber field.

Thank you everyone for the input!!

--------------------
Jason VanKirk
Clarksville, TN

"If you would not be forgotten, as soon as you're dead and rotten, do something worth writing, or write something worth reading." - Benjamin Franklin
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th April 2017 - 03:03 AM