Appending table to existing table

[eagletond]

I have a database, which needs to be updated monthly with new data from Excel. The best way I have found to do it, is to import from Excel and create a new table for the new months data then append the new table to the existing one.

The database and the new one create for each month have an Autonumber ID field, which is the primary key. When I'm appending the new data I tell Access not to create a primary key and I basically ignore the ID field.

The append works fine, but if for example, I have 100 entries and the ID field (using Autonumbering) numbers run from say 1 to 99, the newly appended data does not run in sequence. For example, the first entry, rather than being 100 will be a much larger number, e.g. 41,100.

Why does the sequencing get out of whack?

 

[AlexHedley]

The AutoNumber in the existing Table will increase on every append.

Have you imported/deleted any test data?
Does it start at a high number but increase in order?

 

[eagletond]

The autonumber for the index is increasing, but it starts numbering from a much higher number, e.g. it misses 40,000 number before restarting the index sequence numbering. The sequenced does increase in order.

The records do seem to add correctly though but I don't know why it doesn't pick up from the next number in the sequence.

 

[Galaxiom]

If you care about the number sequence you should not be using autonumber.

The only way to ensure a sequence is to actively insert the number. Loop though a recordset based on the imported data and append it record by record to the destination table as you increment the number.

The append can be done by writing to a recordset or issuing database commands.