Re: Adding records to a SQL 2000 table with an identity field, in Access 2003

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Kyle O via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxx> wrote in
news:524C9FAB4378D@xxxxxxxxxxxxxxxxx:

> I have a form in Access 2003 that
> grabs data from text boxes and places them in 9 different tables ( all
> related by an ID column) which are linked to a SQL Server 2000
> database.

In ado it's something like:

' need a single connection in order to use @@identity;
' i.e. don't use the the currentproject() function each time
set conn= currentproject().connection

' update the parent table first
adoSQL = "insert into parenttable (field2, field3) " & _
"values(N'" & textbox2 & "', " & textbox3 & ")"
conn.execute adoSQL

' get the new identity value
adoSQL = "select @@identity as newvalue"
set rs = new adodb.recordset
' can't remember the arguments off hand, but you get the
' picture...
rs.open adoSQL, conn, adoreadonly
dwNewRecordID = rs!newvalue
rs.close

' now update the other tables
adoSQL = "insert into othertable (recordID, field2) " & _
"values (" &dwNewRecordID & ", N'" & textbox4 & "')"
conn.execute adoSQL, etc, etc


If you use the DAO AddNew... Update methods you can get the identity
value straight out of the new record like this

' don't want to lock any existing records..
jetSQL = "SELECT * FROM ParentTable WHERE FALSE"
set rs = db.OpenRecordset(jetSQL, dbOpenDynaset)

' begin the update
rs.AddNew
' touch the empty record
rs!RequiredField = textbox2
' now the identity will be there to read
dwNewRecordID = rs!RecordID
' and finish the update
rs.Update
rs.Close

' now you can use the dwNewRecordID anywhere else


Hope that helps


Tim F

.