change autoincrement in access to add a starting value

[wondering why access is so bad]

why do they have such a complicated method of changing a starting value in an
autoincrement field type. Every other normal program allows you to enter a
starting value in the design. I do not understand creating a temp table and
an insert query, deleting the temp table and all of the other BS to do
something so simple

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...3f8&dg=microsoft.public.access.tablesdbdesign

 

[Vincent Johns]

Perhaps they could allow some other value, but there might still be
limitations. Would you want them also to allow you to specify that the
subsequent ones be multiples of 7, for example? I can think of a couple of
ways around having to create a separate table. (1) Insert a bunch of dummy
records to use up the numbers you don't want, then delete the dummy records.
Those numbers won't be reused. (2) Instead of Autonumber type, use Number
and set its index to (No Duplicates). Or leave it blank and fill in the
values later via an update query.

 

[Vincent Johns]

Sorry... I meant to add this note on an alternate method for Autonumber:
If you choose to set up [NewNum], a Number-type field with a (No Duplicates)
index, you can simplify entering the values in Table Datasheet view (though
it's probably better to use a Form) by setting the [NewNum] field's Lookup
property to a query whose definition is something like "SELECT 1+Max([Mailing
List].[NewNum]) FROM [Mailing List];". After you enter the first record, the
others will ascend from there.

 

[Tim Ferguson]

=?Utf-8?B?d29uZGVyaW5nIHdoeSBhY2Nlc3MgaXMgc28gYmFk?= <wondering why
access is so (e-mail address removed)> wrote in

why do they have such a complicated method of changing a starting
value in an autoincrement field type.

It's a one-line SQL statement. What is complicated?

Every other normal program
allows you to enter a starting value in the design.

I don't really know what you mean by "normal program"... different
database Management systems will have different methods. In general,
autoincrement fields are not really meant to have user-definable contents
and IDENTITY(1,1) is a pretty solid default.

I do not
understand creating a temp table and an insert query, deleting the
temp table and all of the other BS to do something so simple

Neither do I. What about something like

INSERT INTO MyTable(MyAutonumberField) VALUES (MyStartValue - 1)

- don't forget to include any other required fields. You might want to
delete the inserted record if it is important to your application.
Perhaps it might be a good idea to learn something about Access before
accusing it of being hard?

B Wishes


Tim F

 

[peregenem]

why do they have such a complicated method of changing a starting value in an
autoincrement field type. Every other normal program allows you to enter a
starting value in the design. I do not understand creating a temp table and
an insert query, deleting the temp table <<snipped>>

That's not the only way. See:

http://support.microsoft.com/default.aspx?scid=kb;en-us;202121

From Access2002 onwards, you can put into 'ANSI query' mode and execute

the DDL natively.