appending error

[molly977]

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.

[twinnyfo]

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

[molly977]

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.

[Rabbit]

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.

[twinnyfo]

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

:-)

[NeoPa]

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