By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,255 Members | 1,701 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,255 IT Pros & Developers. It's quick & easy.

appending error

P: 2
Hi, I recently appended some data from the excel sheet to database in Microsoft access. It did append all the data however, the auto increment column of the table increase (jumped) to huge number. Instead of having ST121, it appeared ST4832 in the existing table. Can you please tell me the reason behind it? Now when I add data the primary key column is ST4833 although I delete the appended data.

Thank you so much.
Jun 25 '14 #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
Your autonumber field will always start at the next highest value, even if you delete the appended records.

Your autonumber may be reset, by compacting the database (must be the database where the table reside--no linked tables).
Jun 25 '14 #2

P: 2
Thank you for replying @Twinnyfo. Actually, my table is linked with 6 other tables so resetting auto number will be pain in neck. Isn't there any other alternative way? I am in huge trouble.
Jun 27 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
Do you have a bunch of blank records? Importing from Excel tends to also import a bunch of blank rows because it's really bad at knowing where the data ends.
Jun 27 '14 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
Molly,

My first question is, "Is there a particular reason why the records can't start at ST4833 instead of ST121?" I know from personal experience, as a well-practiced OCD-er, that this would probably drive me nuts, also. However, a record number is a record number--that's all. The auto-number is used as an index to recognize unique records in the system, and nothing else.

Second, if there is a specific reason why the records must be sequential, then having the your record number be part of an auto-number sequence is ill-advised, because any deleted records will leave a gap in the auto-number sequence. Determine another method for calculating out the next sequential Record. This may require some thinking about how to do that, especially if it have letters and numbers (not to mention what would happen, based on your current convention when the number goes to ST1000 from ST999). All this can be done, and probably rather easily.

However, in order for us to effectively guide you to a solution, we would need some additional information.

BTW, based on your post, so far, I do not believe you are in "huge trouble"...

:-)
Jun 28 '14 #5

NeoPa
Expert Mod 15k+
P: 31,768
@Molly.

Your explanation indicates you have an AutoNumber issue but also that your index values contain text. These can't both be true. Please explain your situation more clearly to avoid much wasted time and effort.

I'm only not deleting the question as many have already got involved. The necessary quality of a question here on Bytes.com is a deal higher than this.
Jun 28 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.