problem with inserting in two tables

[michel]

Hi,

i have to insert values into two tables in one transaction.
The first table (base table) has a primary key (type 'integer') which is
incremented automatically with each insert.
The second table (detail table) contains a field (type 'integer') which must
get
the value of the primarty key of the last record in table 1.
So if the primary key value of the last record in table1 is e.g. 24, then
one field in table 2 must get that value.

I tried this but doesn't work: (nrec is always 0)

Dim nrec As Integer

insert into table1 (field1, ....) values( ....)
nrec = comd.ExecuteScalar()
comd.ExecuteNonQuery()
.....
insert into table2 (field1, ....) values(nrec ....)
comd.ExecuteNonQuery()


Thanks for help
Michel

 

[michel]

Thanks

Even better than 'in one transaction' is to do it in one SQL
statement: create a VIEW (virtual table exposed as a Query object
Access) that joins the two tables using an INNER JOIN and INSERT via
the VIEW e.g. simple example (using ANSI-92 Query Mode):

CREATE TABLE Test1 (
field1 INTEGER IDENTITY(1, 1) NOT NULL UNIQUE,
field2 INTEGER
)
;
CREATE TABLE Test2 (
field1 INTEGER,
field2 INTEGER
)
;
CREATE VIEW View1
AS
SELECT T1.Field1 AS T1_F1, T1.Field2 AS T1_F2,
T2.Field1 AS T2_F1, T2.Field2 AS T2_F2
FROM Test1 AS T1 INNER JOIN Test2 AS T2
ON T1.Field1 = T2.Field1
;
INSERT INTO View1 (T1_F2, T2_F2) VALUES (55, 99)
;

Jamie.