Teedee Access VBA Teedee 2010
Initialise Table ID
Home Tutorial Code Snippets

Initialise Table ID

Resets the Primary Key counter to preset number

Requirements

None

Code Snippet

Public Function ReinitialiseTableIdentity(Tablename As String, Optional StartValue As Long) As Boolean     'Resets the Identity Value of the nominiated table         Dim Rs As ADODB.Recordset     Dim sqlText As String         If IsMissing(StartValue) Then StartValue = 0     Set Rs = New ADODB.Recordset     sqlText = "DBCC CheckIdent(" & Chr$(34) & Trim(Tablename) & Chr$(34) & ",RESEED," & Trim(str(StartValue)) & ")"     On Error Resume Next     Rs.Open sqlText, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText     If Err Then         MsgBox "Error: " & Error$, vbOKOnly + vbInformation, "Re-Init Error"         ReinitialiseTableIdentity = False     Else         ReinitialiseTableIdentity = True     End If     On Error GoTo 0     Set Rs = Nothing End Function
Place all code in a module and call the function from your code