Help Getting Key for New Record

[GeorgeAtkins]

I use an ADO connection to add new records to a SQL Server 2K database. After
a
new record is added iit is supposed to be the current record. Yet, I cannot
seem to retrieve the primary key (autogenerated) for the record. I just get a
zero. The added data goes in and can be queried and viewed. Here is a code
snippet:

Dim cnSQL As ADODB.Connection ' SQL server
Dim rsTR As ADODB.Recordset ' SQL table

Set cnSQL = New ADODB.Connection
Set rsTR = New ADODB.Recordset ' transcript table
Set rsGr = New ADODB.Recordset ' source data

rsGr.Open "qryDataForTranscript_V2", cnACC, adOpenForwardOnly,
adLockReadOnly ' ** This recordset contains the data I append into SQL Svr.

With rsTR
.Open "TranscriptCourse", cnSQL, adOpenStatic, adLockPessimistic
.AddNew
.Fields("personid") = rsGr.Fields("personID")
.Fields("coursenumber") = rsGr.Fields("coursenum")
.Fields("courseName") = rsGr.Fields("ALC Coursename")
.Update ' force update in place.

Debug.Print "New key is "; .Fields("transcriptID")

End With
' ******** end of snippet

The debug.print displays a zero for the transcriptID value. how can I get
the current primary key value of the new record?

I am using the following ADO libraries in Access 2003:
MS ActiveX Data Objects 2.8 library
MS ActiveX Data Objects Recordset Libarary

Thanks for any ideas!

George

 

[Alex Dybenko]

Hi,
you can use @@Identity:

Debug.Print "New key is " & cnSQL .Execute("Select @@Identity")(0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com