This is the CORRECT way to get the Auto Increment ID from a database after Insert using SQL.

• Assumptions:
• SQL is a valid Insert statement.
• DSNString is a valid Connection String
• I am using ADO2.5 (I do not know if this works with other versions - if not then this is a good reason to get it)
• I tested this on SQL7 as well as ACCESS 2000 using Jet4

I know that this has driven most people crazy.

Here is the code function

Public Function ExecuteID(SQL As String) As Long
'Tested with ACCESS 2000 and SQL7.0 using ADO2.5
    On Error GoTo LocalError
    Dim cn      As New ADODB.Connection
    Dim rs      As New ADODB.Recordset
    Dim AutoID  As Long

    With rs
'Prepare the RecordSet
        .CursorLocation = adUseServer
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Source = "SELECT @@IDENTITY"
    End With

    With cn
            .ConnectionString = DSNString
            .CursorLocation = adUseServer
            .Open
            .BeginTrans
            .Execute SQL, , adCmdText + adExecuteNoRecords
        With rs
                .ActiveConnection = cn
                .Open , , , , adCmdText
                AutoID = rs(0).Value
                .Close
            End With
            .CommitTrans
            .Close
        End With
        Set rs = Nothing
        Set cn = Nothing
'If we get here ALL was Okay
        ExecuteID = AutoID
Exit Function
LocalError:
        LastError = Err.Number & " - " & Err.Description
        If cn.State = adStateOpen Then
            cn.RollbackTrans
            cn.Close
        End If
        Set rs = Nothing
        Set cn = Nothing
        ExecuteID = False
End Function

Brian Gillham
MailTo:Brian@FailSafe.co.za
FailSafe Systems
http://www.failsafe.co.za