NextID_Reset

Checks if ID has been reset in SQL database table
If yes, it will reset it and returns status
Needs DB_Connect_Install, mMain.oRS


Public

Not Tested

My Own Work
Function NextID_Reset(SQLTable, Optional AskUserToreset = 0)
    ' NextID_Reset = 0 means no query could be executed here, connection? driver?
    ' NextID_Reset = 1 query executed, IDs are fine, no changes applied
    ' NextID_Reset = 2 we needed to reset IDs since they were offset
    '
    SQL2 = "Select IDENT_Current('" & SQLTable & "')+1 NextID,(Select Max(ID) from " & SQLTable & ") LastID"
    ' Expected output
    '    NextID        LastID
    '    20025            4556
    DB_Connect_Install SQL2
    NextID_Reset = 0
    If Not mMain.oRS.EOF Then Exit Function
    Val1 = mMain.oRS(0)
    Val2 = mMain.oRS(1)
    If Val1 = Val2 + 1 Then
        ' We are good, continue
        NextID_Reset = 1
    Else
        if AskUserToreset = 1 then
            Mss = MsgBox("Found Shifted ID !!!" & vbCrLf & "Next ID: " & Val1 & vbCrLf & "Last ID: " & Val2 & vbCrLf & vbCrLf & _
                "Will reset and continue.", vbCritical + vbOKCancel)
            If Mss = vbCancel Then End
        End If
        SQL3 = "DBCC CheckIdent (" & SQLTable & ", Reseed, " & Val2 & ")"
        DB_Connect_Install SQL3
        NextID_Reset = 2
        DoEvents
    End If
End Function

SQLTable, Optional AskUserToreset

Views 625 Downloads 216

VBA-Excel Database
ANmarAmdeen
787
Attachments
Revisions

v1.0