reset autonumber value in table

[Elizabeth]

Anyone have a routine to reset an autonumber field to 1?

 

[Allen Browne]

After deleting all records, compact the database:
Tools | Database Utilities | Compact

 

[Jeff Boyce]

Elizabeth

Why? If you're using/showing autonumbers and expect them to be
"meaningful", you'll be disappointed. Access autonumbers are intended
primarily as arbitrary row identifiers, to allow tables to be related to
each other.

 

[Guest]

Because:
the tables in question are used in data load routines,
repeated at regular intervals. The autonumber values
indeed are arbitrary row identifiers but grow to be very
large with each new load.

I queried newsgroup to see if anyone already set up a
routine -- in VB perhaps -- to reset the beginning value.

 

[Allen Browne]

As well as just compacting the databsae, in Access 2000 and later you can
use ADOX to reset the Seed property of the AutoIncrement field.

Syntax to use in the Table of the Catalog:
.Column("MyColumn").Properties("Seed") = 1

 

[david epsom dot com dot au]

ADOX example from Microsoft

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As
Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you would like to use for next
autonumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

(david)

 

[Jeff Boyce]

You must be doing some very seriously large loads, then. I believe the
Access Autonumber uses values in the billions -- just how many rows are you
loading?!

 

[Guest]

Thanks! I will try this.

-----Original Message-----
ADOX example from Microsoft

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As
Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you would like to use for next
autonumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

(david)




.