by Quake (33 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Intermediate
Date Added: Mon 10th February 2020
Rating: 
(0 Votes)
Command to Reset ID / INDEX Counter. See * New for a Fix if you are
having issues if you ran this code on a populated Recordset.
VBcoders' History is your Table
' Index is the column you wish to reset
' ***************************************************************************************
Sql = "ALTER TABLE [History] ALTER COLUMN [Index] COUNTER(1, 1);"
CON.Execute Sql
' ***************************************************************************************
' WARNING!
' Using this method will reset the Auto Counter to the lowest value.
' If you are using Recordset's that refer to an Index than, DO NOT DO THIS.
' If you have a Table that you do not care of the Index Value then this will help you.
' NOTES:
' This will reset to the lowest value AVAILABLE. So if you have 1, 3,4,5, 6, 9,21, and so on.
' It will start from the Value of 2 and go up from there. Leaving all other records as they are.
' If other records are present then it'll skip to the next available count
' If you are DELETING the whole record count you can do this.
Dim strSQL As String
' OPEN YOUR DATABASE
Call Open_DB
strSQL = "DELETE ALL * FROM [History]; "
CON.Execute strSQL
strSQL = "ALTER TABLE [History] ALTER COLUMN [Index] COUNTER(1, 1); "
CON.Execute strSQL
Me.Caption = "History was Deleted. Counter was reset."
' CLOSE YOUR DATABASE
Call Close_Con
' ****************************************************************************************************************
' The above code DELETES ALL and Resets the Counter back to 1
' Take NOTE: If you use NO DUPLICATE statement in one of your columns this could conflict with it.
' You can run a Compact/Repair to re-order the counter. If you are not DELETING the ENTIRE Recordset.
' ****************************************************************************************************************
' NEW
' ***************************************************************************************
' If you ran this code on a populated Database Table and now are having issues.
' Here's a Fix
' USEAGE: Call Fix_Me(YOURTABLE)
' ***************************************************************************************
Sub Fix_Me(byVal sTable as String)
'On Error GoTo Err_Proc
Dim Sql As String
Dim sCnt As Long
Dim sIndex As Long
Dim sCur As Long
sCur = 0
' OPEN YOUR DATABASE
Call Open_DB
' OPEN YOUR TABLE
Call Open_RS(sTable)
If Not (RCS.RecordCount = 0) Then
sCnt = RCS.RecordCount
RCS.MoveFirst
While Not RCS.EOF
sIndex = RCS.Fields("Index").Value
If sIndex >= sCur Then sCur = sIndex
RCS.MoveNext
Wend
End If
' CLOSE YOUR TABLE
Call Close_rcs
sCur = sCur + 1
Sql = "ALTER TABLE [" & sTable & "] ALTER COLUMN [Index] COUNTER(" & sCur & ", 1);"
CON.Execute Sql
' CLOSE YOUR DATABASE
Call Close_Con
Exit Sub
Err_Proc:
End Sub
' ***************************************************************************************
' What this does is it gets the last Highest Index(Ubound(Index))
' Gives it a +1 and ALTERS the Table INDEX to that Highest Index.
' You'll need to setup your Open Database
' and Open Recordset if You haven't already.
' ***************************************************************************************
' Dream Possibilities
' SubZero DeZigns
