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 262 Downloads 85

VBA-Excel Database
ANmarAmdeen
725
Attachments
Revisions

v1.0