Upgrading Database - Importing Autonumber Field (1 Viewer)

Hi all,

I have an issue which surely is relatively common, however I have struggled to find an answer. I have built databases for 3 similar projects (after school music clubs). The first one was built in Access 2003. The other 2 in Access 2013. I'm self taught, but have learned a great deal about database design between databases 1 and 3, and now I'd like to upgrade the older 2, to take advantage of improvements I've added to the new one.

Structurally they are relatively similar, but the main issue I have is that Primary keys on tables such as ChildID on table T_Children, InstrumentId on T_Instruments, AdultID on T_Adults etc are all Autonumber fields.

This means I can't simply copy and paste existing data into a blank version of the new database.

So what do I do? :banghead:

Was it bad practice to use autonumber fields as primary keys on these tables? How do I get around this, and what steps should I take to make future upgrades easier?

Many Thanks for your time, wisdom and understanding.

Alan

Docmd.RunSql "Select * Into newTableName In yourNewDatabase From T_Children"

run this on your old db.
do this with the rest of your table, it will copy the autonumber field exactly as they are numbered.

as arne says, you can insert an autonumber into a table, and the autonumber seed for the table will reset as required

Thanks guys.

Am I right in saying that this is basically just the same as importing the table from one database to another? So starting point would be that the table simply doesn't exist in the new database?

I have added various new fields to the tables, added descriptions where there weren't descriptions, limited field lengths when in the past they were all set to 255 etc. So I'd ideally like to keep the new tables I've built but import the data.

Is this possible?

Thanks,

Alan

if you already made a table in new db, you can use:

DoCmd.RunSQL "INSERT INTO NewTable IN NewDataBase SELECT * FROM T_Children;"

Thanks Arnel, that's done the trick!

Alan