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 |