Insert into 2 tables

[HC]

Hello,
I am trying to insert a new row into a table A, then get
the new generate primary key number(autonumber) from table
A insert into table B.

I am currently using a select statement to get the max
number through ado.recordset, then insert into table B.

If there any better way to do this? I am afraid my method
might have a possibility of getting the wrong number to
insert into the B table.

Thanks

HC

[Val Mazur]

Hi,

If you use SQL Server then you can write trigger on INSERT
for first table. It will fire when you will try to insert
new record into first table. In that trigger you can write
statement which will insert new record into second table.
Check BOL to see how to write triggers.

Val

>.
>

[Joe]

Hi,

if your dbms provides transactions, try this way:

- begin transaction (other users are locked now)
- insert record into table A
- get maximum key from table A
- insert other record into table B
- commit transaction
or, if something went wrong
- rollback transaction

This way does not provide highest performance but it is the safest one. The
use of triggers is considerably faster, but you should use a transaction
anyway.

Regards,
Joe

"HC" <sl...@yahoo.com> schrieb im Newsbeitrag
news:9eac01c15667$cb9081b0$19ef2ecf@tkmsftngxa01...