Need Help getting current key value after adding ADO record in SQL

[Guest]

I use an ADO connection to add new records to a SQL Server 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!

 

[Brendan Reynolds]

Looks like you need to use a different cursor type. This worked for me when
I changed the cursor type from adOpenStatic to adOpenKeyset ...

Public Sub TestGetId()

Const strcConnect As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set cn = New ADODB.Connection
cn.ConnectionString = strcConnect
cn.Open
Set rst = New ADODB.Recordset
' rst.Open "SELECT * FROM Categories", cn, adOpenStatic, _
' adLockPessimistic
rst.Open "SELECT * FROM Categories", cn, adOpenKeyset, _
adLockPessimistic
rst.AddNew
rst.Fields("CategoryName") = "Test"
rst.Fields("Description") = "A Test Category"
rst.Update
Debug.Print rst.Fields("CategoryID"), _
rst.Fields("CategoryName"), rst.Fields("Description")
rst.Close
cn.Close

End Sub