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
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
' 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 1,154
Downloads 389
CodeID
DB ID