Move Record from one table to another

[Djblois]

How do I copy a Record from One Table to a different table and then give it a different Primary Key based on the new table?

 

[DCrake]

Try using an Append query

 

[Djblois]

Ok An Append Query seems like it would work well. However, how after I run that query do I get it to open with that record in a form? I know i can open it by the primary key but how would I capture the primary key?

 

[DCrake]

Why do you need to append from one to another anyway?

 

[Djblois]

Because we have an Oracle table that has all our products. However, sometimes we take samples of Products that we do not carry yet but are similar to other items that we do carry. So I needed to create a Temporary Product Table in Access and people can say this product is similar to that product in Oracle and it will copy it over and give the person the ability to make any changes that they need.

 

[gemma-the-husky]

tricky

i would be inclined to have a "similaritems" table with a FK in the new table linking to the PK of the Oracle table.

It starts to get tricky when the product may now come directly from the oracle table, or from the new table. It probably needs some careful thought.

Is the Access dbs completely independent of the Oracle one?

 

[Djblois]

Well I do not know what you mean by Independent exactly? I connect to the Oracle table through ODBC and pull information from it but I never write to the Oracle Table in Access.

All I want it to do is Copy some of the fields from the Oracle table into the new table and then open the New Item in a form I created so a user can edit it. The reason is the items will be similar but not exactly the same - so they need it open to edit it.