Return Value from INSERT Statement (PK autoincrement) 1

[svagelis]

Well, I m using ADO - VB6.0 and an Access Database.
Through a command object i m commiting SQL STATEMENTS to my mdb file.
When I m issuing an INSERT Statement to insert new record i want to get back the primary key of the record ( Auto increment field declared in Access).
So far i had to deal with SQL server. SQL server has the @@identity variable for that job.

I ve looked in MSDN and found that the same problem is faced with a Select statement after the update p.e.

DIM objCmd as NEW ADODB.Command
DIM objRS as New ADODB.RecordSet

objCmd.CommandText="Insert INTO Cust VALUES('John','Smith')"
objCmd.Execute
objRS.open "SELECT MAX(ID) AS Cust_ID FROM Cust"
lngIdentity = objRS!Cust_ID
objRS.Close


Well i dont like this way, i think it gets to slow.
Any other way to do this ??

Thanks in advance

 

[PraveenMenon]

You can have a public variable to store current number of records.. but unless your Data Access Functions are organised, it cud be hard to implement. All the Best
Praveen Menon
pcmin@rediffmail.com

 

[zemp]

Unfortunatly there is nothing in Access similar to the @@identity variable in SQL server. As a result you will have to make another call to the database to fetch the newly assigned primary key. Thanks and Good Luck!

zemp

 

[svagelis]

Well i 've found that Jet Provider v4.0 supports @@identity.
All I have to do is to State a RS.open "SELECT @@identity" to get the identity of the last record inserted.
I ve put this query in an access query and called it from my app and it works fine. I just tested a simple example and there's the code:

tbl_cust is table with customers (in Access)
qry1 is the query "Select @@identity" (in Access)


Dim objConn As New ADODB.Connection
Dim objcmd As New ADODB.Command

Private Sub Command1_Click()
Dim objRS As New ADODB.Recordset

Set objcmd.ActiveConnection = objConn
objcmd.CommandText = "INSERT INTO tbl_cust (Cust_Desc) Values ('blbbla')"
objcmd.Execute

objRS.Open "qry1", objConn, adOpenForwardOnly, adLockReadOnly
MsgBox objRS(0)

objRS.Close
Set objRS = Nothing

End Sub

Private Sub Form_Load()
strPathToMDB = App.Path & "/db.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
objConn.Open strConn

End Sub

Private Sub Form_Unload(Cancel As Integer)
objConn.Close
Set objConn = Nothing
End Sub

http://support.microsoft.com/default.aspx?scid=KB;en-us;q232144

 

[zemp]

I stand corrected. It pays to look closely at the improvements in newer versions.

Good work, a star for you. Thanks and Good Luck!

zemp