SQL Server Insert

[Bill Crawley]

Hi All,

I have a table where the Key is auto generated by the database.

Once I have performed my insert into this table, I need to get that record immediately and then insert into another table using the Key generated as the Foreign key to the second table. How do I ensure that I can gaurentee that I am obtaining the correct record from the first table, I cannot garuntee that any other columns on my first table will be unique.

[Thread1]

Did you try to take advantage of the recordset object?


rs.AddNew
rs("Field1") = "Value1"
rs("Field2") = "Value2"
rs("Field3") = "Value3"
rs.Update

MsgBox "The newly inserted record has a key " & rs.Collect("<Auto-generated field name>")

[Bill Crawley]

HI,

Part of the problem is that I need to gaurentee that I obtain the correct key, since there could be multiple entries into the database.

It appears that on the SQL Insert Statement, If I use the syntax:

Insert into .........Values........Select myidcol=@@identity

This should return the generated key for the newly inserted record.