• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• MDB ADO SQL RESET

MDB ADO SQL RESET

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.

Rate MDB ADO SQL RESET

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

' 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