Capturing Primary Key of newly inserted rows

Question

I have 2 tables with a one to one relationship. I need to insert information into these two tables using the same primary key that is unused. I have the first table being inserted into with a simple insert into query. Is there a way to grab the primary keys of the records after they are inserted and the primary key is auto incremented? I do not think it is safe to just add to the 2nd table because the some records dont have their 2nd table counterparts.

Is it safe to query for the bottom x values based on the query used to insert?

The values are coming from a source table that has fields A, B, C, D, E, and F

Table1 has fields id1, A, C, E, and F and Table2 has fields id2, B, and D. id1 and id2 are equal but not the same as the id from the source table

I suppose I should Add this is all within a VBA module, Calling the query as a querydef object — Evan, Sep 30 '14 at 17:33
How do you identify what should be in Table1 and not in Table2? — Mark C., Sep 30 '14 at 17:37
The values are coming from a source table that has fields A, B, C, D, E, and F Table1 has fields id1, A, C, E, and F and Table2 has fields id2, B, and D. id1 and id2 are equal but not the same as the id from the source table — Evan, Sep 30 '14 at 17:45

score 0 · Answer 1 · answered Sep 30 '14 at 19:35

In your application code generate a random 64 bit integer. Use that as your primary key for the inserts into the two tables.

If the insert fails because of the birthday paradox, congratulate yourself, because your application is very popular!

(According to wikipedia, the odds of a SINGLE collision after 100,000,000 inserts is less than 0.1%. Ask yourself what are the odds of a single insert failing out of 100,000,000 for some other reason in your application during the same period before eschewing this approach.)

http://en.wikipedia.org/wiki/Birthday_attack

score 0 · Answer 2 · answered Sep 30 '14 at 20:50

In pure TSQL on SQL Server, you can use the OUTPUT clause to output the results of an INSERT. I don't know if this is available for Access, but I thought I would mention it.

For example:

create table Test
(
    TestKey int identity(1,1) primary key clustered,
    TestValue int
)

insert into Test (TestValue)
output Inserted.*
select 1
union select 2

-- results:
-- TestKey   TestValue
-- 1         1
-- 2         2

You can redirect this output into a table if you want. For example:

create table #TempTable
(
    TempTableKey int,
    TempTableValue int
)

insert into Test (TestValue)
output Inserted.TestKey, Inserted.TestValue
into #TempTable (TempTableKey, TempTableValue)
select 1
union select 2

select * from #TempTable

-- results:
-- TempTableKey TempTableValue
-- 1            1
-- 2            2

Yeah, Sometimes I wish they would just give me an SQL server to work with but I am restricted to Access which only uses sql, not tsql. Thanks though — Evan, Oct 01 '14 at 12:01

Gord Thompson · Answer 3 · 2014-09-30T21:26:33.890

[Table1].[id1] should be an AutoNumber field. [Table2].[id2] should be a Long Integer field. Perform the INSERT into [Table1], retrieve the newly-created AutoNumber value using SELECT @@IDENTITY and then use that value for [Table2].[id2], something like this:

Dim cdb As DAO.Database, rst As DAO.Recordset, sql As String, newId As Long
Set cdb = CurrentDb
' insert one row into [Table1]
sql = "INSERT INTO [Table1] ([A], [C], [E]) VALUES ( ... "
cdb.Execute sql, dbFailOnError
' retrieve the newly-created AutoNumber value
Set rst = cdb.OpenRecordset("SELECT @@IDENTITY", dbOpenSnapshot)
newId = rst(0).Value  ' this is the new key value to use for [Table2].[id2]
rst.Close

score 0 · Answer 4 · answered Oct 01 '14 at 12:03

Well these are all good answers but what I ended up doing was performing the insert into the first table, requeried the form, and then used vba code to search the form's recordset and insert values onto the form controls. This allowed the form query which had the relationship take care of inserting the right key for me.

Capturing Primary Key of newly inserted rows

4 Answers4