[AccessD] Resetting Autonumber seed

Robin Robin at rolledgold.net
Wed May 10 13:04:27 CDT 2006


Gustav, Matt,
The problem is deleting Orders from one table and transferring to Posted
Table via queries- Database is compacted overnight which resets the seed
to the lowest order no left - this results in duplicate order numbers
when they are eventually transferred...

Thanks for your help so far - must leave this for tonight...!!

Will return to it tomorrow

Rgds
Robin
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 10 May 2006 19:02
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Resetting Autonumber seed


Hi Robin

No, the Counter syntax works in Access 2000+.

But why at all trouble with resetting of Autonumber? It's generally a
very bad idea.

/gustav

>>> Robin at rolledgold.net 10-05-2006 19:28:30 >>>
Gustav,
Read a bit further through this and this is exactly what I need - 

Both the seed and the increment can be modified using an ALTER TABLE
statement. New records inserted into the table will have values that are
automatically generated for the field based on the new seed and
increment values. If the new seed and increment can yield new values
that match existing values generated by the preceding seed and
increment, duplicates will be created. If the field is a Primary Key,
then inserting new records may result in errors when duplicate primary
keys are created. 
The following is an example of using the ALTER TABLE statement to set
new seed and increment values for an auto-increment field:

 ALTER TABLE TableName ALTER COLUMN FieldName SET IDENTITY (2,4)
				
Are you thinking I can't do this because I'm using Acess97 ? (I've tried
sbstituting IDENTITY for COUNTER but get the same error) Rgds Robin


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list