After data import primary key value increased significantly

[Beemerang]

Hi All

I have imported a list from Excel into my database. The primary key is long integer (Indexed no duplicates). After the (successful) import, the next primary key assigned to a new record has jumped from (for example) 15 to 30024455. Why would this be? I have compacted and repaired the database (Access 2007) in case it was some kind of corruption but with no effect. Any primary keys on tables where I have not imported data works as expected i.e. incremented by one.

Does anyone have an idea what could be causing this?

Thanks
Beem

 

[WaiveIt]

If the PK is an Autonumber check the New Value setting before and after the import. -> "Increment" or "Random" setting.

How does the import work? Do you 'CREATE' tables? Somewhere in your subroutine do you have any set properties clauses?

 

[Beemerang]

If the PK is an Autonumber check the New Value setting before and after the import. -> "Increment" or "Random" setting.

How does the import work? Do you 'CREATE' tables? Somewhere in your subroutine do you have any set properties clauses?

Hello and thanks for the reply!

This is a once-off import so I did it manually using the Excel import functionality instead of going the VBA route.

The new value property is set to "Increment" for each of the 3 tables this is happening on. I have verified that the field which contains the primary key in the Excel spreadsheet was formatted as a number.

 

[Mihail]

... or/and remove all data from that table then do a Compact and Repair. After all this retry the import.

 

[Beemerang]

I would not import a field from Excel to Access destined to be a primary key. I would exclude that column from my import / export. Let Access handle that part through its internal process.

Thanks for the tip but I need to import it as it is a ore assigned unique ID that cannot be auto generated.

 

[Beemerang]

... or/and remove all data from that table then do a Compact and Repair. After all this retry the import.

Thanks for this. It was a brand new table into which I imported the data. Do you think it still makes sense to repeat the process?

 

[Rabbie]

It should not matter if your PK values are not incremental as long as they are unique. It is not good practice to use a PK for anything than as a record selector

 

[Beemerang]

Thanks for the reply Rabbie. I am just a little concerned since I can't fathom the reason for this phenomenon. I'll forge ahead and hope for the best!

Beem