copy table maintain autonumber

[Jason]

Hey all,
I am trying to copy all the data from a db w/ nearly
the same table headers (some have been just renamed). My
question is how do i keep the correct numbering (its all
autonumbered index) that comes with the tables i am
copying from?
Thanks,
Jason

 

[Jeff Boyce]

Jason

You don't mention if the "copy to" table already has an autonumber field,
nor if there are already rows in that table.

To "keep" all the rows from the "from" table, you need to make sure that
none of the autonumbers in that table are already "used" in the "to" table.

You can create an append query to copy rows from/to, specifying which fields
to use.

 

[Jason]

Jeff,
Yes there are autonumber fields already in use. The first
thing i am going to do is clear out any old data and set
the autonumber to normal numbering. then switch it back
so i can start back at 1 (or 0, whichever). That should
work correctly, or am i mistaken?

 

[Jason]

I also forgot to mention that these are tables in two
different DB's. will this affect the query?

 

[Jeff Boyce]

Jason

Provided you can link to both tables, shouldn't make any difference.

But I'm concerned about your previous response, about how there may already
be autonumber values in the "to" table. If that table has any "child"
tables related to it, you'll hose your database!

Perhaps if you described "why" you are doing this (transferring between two
dbs), rather than how you are trying to do it...?

 

[Guest]

Jeff,
I have been working on making changes to a database
while people have continued to work on the old one. I am
going to move all information over. so i believe the
relationships b/w the autonumbering on the child tables
will still work correctly. I will try to link the tables
tomorrow once i get the go ahead from my client. What is
the easiest way (in your opinion) to set up the append
query? I'm not an access guru and haven't ever really set
one up. THanks for all the help
Jason

 

[david epsom dot com dot au]

the easiest way (in your opinion) to set up the append

make copies of the 'old' and 'new' databases

Create an empty database.
Link all the tables in the 'old' database
Link all the tables in the 'new' database
(this will create a second set of links, all ending in '1')

Create an append query for each table.

Create a macro that runs each append query.
Start the macro with 'set warnings off'
End the macro with 'set warnings on'
Put the queries in order, so that the 'one'
tables are filled before the dependent 'many'
tables are filled.

run the macro

Check the 'new' database to see that all is well.

Compact the 'new' database.

(david)