article

MDB ADO SQL RESET COUNTER

Email
Submitted on: 10/28/2016 3:35:55 PM
By: Quake 
Level: Intermediate
User Rating: By 1 Users
Compatibility: VB 6.0
Views: 4837
 
     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.

 
				'*************************
' 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


Other 20 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

12/12/2016 2:27:56 PMAmelia

I really thank you for the valuable info on this great subject and look forward to more great posts. Thanks a lot for enjoying this beauty article with me. I am appreciating it very much! Looking forward to another great article. Good luck to the author! All the best! forskolin
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.