Two Tables Insert Into with Select @@ Identity

  • Thread starter Thread starter ecwhite
  • Start date Start date
E

ecwhite

Hello,

I need help with the sytax to insert into two tables at the same time with a
select @@ identity statement. I have the step one of the clause working but
need help getting the autoincrement Id and then inserting into the second
table e.g

INSERT INTO table1 ( old_ID, rec_nbr, pin_nbr, birth_date)
VALUES
(Forms!frm1!txt_old_ID,Forms!frm1!txt_rec_nbr,Forms!frm1!txt_pin_nbr,Forms!frm1!birth_date)

I need to Select @@ Identity and then insert it as the unique Id for table2

INSERT INTO table2 ( auto_incrementid,field2, field3, field4)
VALUES
(auto_incrementid,Forms!frm1!field2,Forms!frm1!field3,Forms!frm1!field4);

How do I abort if table1 insert succeds and for some reason table2 insert
fails?


Thanks,
ecwhite.
 
Sorry, I should haVe said that i am using MS Access. sorry for that and I
look forward to your helping me with this problem.

Thanks ecwhite.
 
My front end is MS Access and the backend in MYSQL. I have the tables in MS
Access as links. I will start applying what you have shown me so far but let
me know please if this little bit of information changes anything.

Thank you very much for your help.
 
Back
Top