getting autonumber field value while inserting into two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

getting autonumber field value while inserting into two tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-10 : 08:19:32

Larry writes "Here is the senerio... I have to insert values into two tables and one of the values I need is from the first table (autonumber "ID" Field) so I can insert this value into the second table to keep the relationship. So I need a insert statement that gets me the value of the first table and then insert it into the second table.
Thanks"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-06-10 : 18:48:58

If you are doing this in a module, use an ADO Connection object to issue your commands, and do not close the connection between commands, you can issue your INSERT statement, then immediately follow it with an SELECT @@IDENTITY statement to retrieve the value of the autonumber just used. Then use that value in your second INSERT statement.

Even though most people think of @@IDENTITY just for SQL Server, I have used this successfully in Access 2000.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 21:12:30

Also - depending on how you do your record inserts:

If you're inserting a record using the Recordset.AddNew method, the autonumber field can be read after using the Update method:
eg.

rst.Open TableName, conn
rst.AddNew
rst!Field2 = "value"
rst!Field3 = "value2"
rst.Update
NewId = rst!Field1 ' the field with the autonumber
rst.close

But - given the preference I would use AjarnMark's method. Much more efficient.

Elm
Starting Member

1 Post

Posted - 2004-06-28 : 11:19:52

Hi Larry,

Just to add on to what AjarnMark said. I would also place the insert statements into a transaction to insure that you dont get one table updated and not the other. Below is the code sample from a simple window that would do such a thing. Hope it helps.

Private Sub Command1_Click()
On Error GoTo ErrHandler

Dim strErrorMessage As String
Dim strTargetTable As String

Dim cnnConn As ADODB.Connection
Dim rstWork As ADODB.Recordset

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim lngNewKeyId As Long

' Open the database
Set cnnConn = New ADODB.Connection
cnnConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Projects\tests\TestDB.mdb"
cnnConn.Open

' Begin transaction
Call cnnConn.BeginTrans

' Insert to Table1
strTargetTable = "Table1"
strSQL1 = "insert into Table1(SomeInfo) values ('abc')"
Call cnnConn.execute(strSQL1)

' Get the generated key
strSQL2 = "select @@IDENTITY as NewId"
Set rstWork = cnnConn.execute(strSQL2)
lngNewKeyId = rstWork("NewId")

rstWork.Close
Set rstWork = Nothing

' Insert to Table2
strTargetTable = "Table2"
strSQL3 = "insert into Table2 (Table1KeyId, SomeMoreInfo) values (" & _
CStr(lngNewKeyId) & _
",'xyz'" & _
")"

Call cnnConn.execute(strSQL3)

' Commit transaction
Call cnnConn.CommitTrans

' Close the database
cnnConn.Close
Set cnnConn = Nothing

Exit Sub

ErrHandler:
' Error Handling
strErrorMessage = Err.Number & " - " & Err.Description

If Not (rstWork Is Nothing) Then
rstWork.Close
Set rstWork = Nothing
End If

If Not (cnnConn Is Nothing) Then
Call cnnConn.RollbackTrans
cnnConn.Close
Set cnnConn = Nothing
End If

Call MsgBox( _
"An error was encountered while attempting to insert a new " & strTargetTable & " row." & Chr(13) & _
strErrorMessage, vbCritical + vbOKOnly, "Error")

End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources