Insert values into an autonumber field.

[Dustman]

I've got a db that was originally in FoxPro. All the data was pulled out and put into a large table in access 2000. I had to completely redesign the tables (the old db used 2 tables with over 100 columns and 1 reference!!) and now I can't figure out how to insert the old data. I have the primary key (donorID) in the new table (donors) set to autonumber so all new entries will get the new number. If I just copy and paste the old values from the old table (DP) then the primary key values (DONOR2) are erased and set with the autonumber. I have to keep the new donorID and the old DONOR2 values identical.

If I use the RunSQL command and insert using SQL, I can override the autonumber. This will work but I have 20000 records to do this to.. that isn't going to work for me. How can I make a SQL statement in VBA that will read the number from the old table and write it to the new table?

INSERT INTO donors (donorID) VALUES (???);

I need this info in the ??? part

SELECT donor2 FROM dp;

Does SQL have a method of doing something like this? I'm open to any other suggestion too. The main key is that all the donorID numbers match in the new table because they are referenced to several other tables.

I hope I haven't confused anybody. -Dustin
Rom 8:28

 

[BNPMike]

What I think you are saying is you have two tables each with a (matching) key that you wish to load into one new table. You have loaded the first table but lost the keys somehow so you can't match the data from the second.

Try and avoid programming solutions.

I would import both tables as is. Then generate a Make Table query by joining the two old tables with their still intact keys and use Select * to preserve all the data. You now have a new single table. Now you can add an Autonumber column and delete the existing key if you feel so inclined. I would have thought you might want to preserve the old key in case you need to refer back to the old database at any time in the future. mike.stephens@bnpparibas.com

 

[Dustman]

Thanks for the suggestion but I can't proceed that way. I have to keep the primary keys the same when they are transfered over because of other situations in the DB that I don't have the time to explain. I am manually splitting the 120 column table into about 8 different tables. The donorID from the old table (dp.donor2) did not remain as autonumber when it was transfered from FoxPro therefore, I must re-enter all the existing donorID's in the new table where the column is autonumbered. As I said earlier.. using an INSERT INTO sql statemnt.. this is allowed. I just need some way to read the old values (maybe 1 at a time and use a loop) and insert them into the new table. -Dustin
Rom 8:28