Reset autonum field programatically?

  • Thread starter Thread starter Guest
  • Start date Start date
Hi, Wayne.
Is there any way to reset an autonum field with Basic code (in an emptied
table)?
Click to expand...

Yes -- if you are using Access 2000 or newer. As long as the table is
already empty, just reset the AutoNumber column's Seed Property. First, set
a reference to the Microsoft ADO Ext. 2.x for DDL and Security Library.
Paste the following into a standard module:

Public Sub resetAutoNumSeed(sTableName As String, sFieldName As String, num
As Long)

On Error GoTo ErrHandler

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTableName)
Set col = tbl.Columns(sFieldName)
col.Properties("Seed") = num

CleanUp:

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in resetAutoNumSeed( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

Use a procedure (such as the OnClick( ) event of a button) to call this
procedure:

Public Sub ResetBtn_Click()

On Error GoTo ErrHandler

Call resetAutoNumSeed("tblStuff", "ID", "101")

Exit Sub

ErrHandler:

MsgBox "Error in ResetBtn_Click( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where tblStuff is the name of the table, ID is the AutoNumber field,
and 101 is the number to be assigned to the next new record in the ID column
in the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
If there are no conflicts with (existing) index numbers and the Access
Version is >= 2000, you can set the next seed for an autonumber with a short
DDL statement.

ALTER TABLE [Tablename] ALTER COLUMN [Fieldname] COUNTER (n, m)
 
Back
Top