gawilliam
asked on
Accesing the Autonumber in an Access Table
I need to access the next number available in an autonumber field of an access table. I could look for the last number used and incremente it by one, but if a record has been added and then deleted, my solution will not be valid. Does anyone knows how to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Public Function GetMaxSequence() As Long
' *** Get the las sequence
Dim record As Recordset
Dim sSQL As String
On Error GoTo ERROR_GetMaxSequence
sSQL = "Select Max(Counter) "
sSQL = sSQL & "From Spool "
Set record = DB.OpenRecordset(sSQL, DAO.dbOpenDynaset, DAO.dbSQLPassThrough + DAO.dbReadOnly)
If (record.RecordCount <> 0) Then
' *** Infos found
GetMaxSequence = CLng(ReadRecordSetField(re
Else
GetMaxSequence = 0
End If
record.Close
Set record = Nothing
Exit Function
ERROR_GetMaxSequence:
LogString "Error in GetMaxSequence " & Error, LOG_TYPE_ERROR
GetMaxSequence = 0
Exit Function
End Function