Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reseed an autonumber field

How To

Reseed an autonumber field

by  redapples  Posted    (Edited  )
There seem to be quite a lot of posts asking how to this and I was looking to do this myself but did not find the answer here.

A simple SQL statement will do this in Jet4.0 SQL

Code: 
Dim cnn as ADODB.Connection
Dim StrSQl as string

StrSQL = "ALTER TABLE tblProblems ALTER COLUMN [Key] COUNTER(1,1)"
    cnn.Execute strSQL

I use this for some tables that store temporary data for producing some reports and as such my data is removed prior to running this SQL. I would not suggest using this on a table where you preserve the data, especially when the counter field is used as a KEY field as I have done.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top