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