What's my record ? 1

[LittleSmudge]

I am using MSAccess 2000 as the GUI front end to a MySQL 4.0.12-max-nt database.

When the user creates a new record I save the data using an ADODB.Recordset via ODBC in the form.

Private Sub SaveRecord(RecId As Long)
On Error GoTo Err_SaveRecord
Dim rstMy As ADODB.Recordset
Set rstMy = New ADODB.Recordset
rstMy.CursorType = adOpenKeyset
rstMy.LockType = adLockOptimistic
rstMy.Open "SELECT * FROM tblTest WHERE TestId = " & RecId, MyConn
If rstMy.EOF Then   ' This is a NEW record
    rstMy.AddNew
End If

rstMy!TestText = TestText
rstMy.Update
' If this is saving a new record then update the screen display of the TPReqId
If IsNull(TestId) Then
    rstMy.Close
    rstMy.Open "SELECT TestId FROM tblTest ORDER BY TestId Desc LIMIT 1"
    TestId = rstMy!TestId
End If
rstMy.Close
Set rstMy = Nothing

Exit_SaveRecord:
Exit Sub
Err_SaveRecord:
If Err.Number = 3709 Then   ' MyConn = nothing
    Call SetMyConn
Else
    MsgBox Err.Description, , Err.Number
End If
Resume Exit_SaveRecord

End Sub

All of the above works. But I'm concerned that if the db gets busy the method of finding out the Record Id of the new record I have just created could not be 100% reliable.

Is there another ( better / more efficient ) way of getting the value of the Auto_Increment field for the record that you have just saved ?

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[sleipnir214]

After the insert, run the query:

SELECT last_insert_id();

This will return the auto_increment value generated for your insert. MySQL maintains this value on a per-connection basis.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LittleSmudge]

Wonderful sleipnir214,

I felt sure there must be a better method that the flaky one I came up with.

Have another star to keep you on the top of the list!







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.