E
Elizabeth
Anyone have a routine to reset an autonumber field to 1?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
After deleting all records, compact the database:
Tools | Database Utilities | Compact
Tools | Database Utilities | Compact
J
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.
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.
G
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.
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.
A
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
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
D
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)
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)
J
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?!
Access Autonumber uses values in the billions -- just how many rows are you
loading?!
G
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)
.