How do I do Identity Insert in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 20 Nov 2005 00:46 GMT

I have an Access table with an AutoNumber that I want to have a '0' value.

In SQL Server I can do an "Identity Insert" to override a column's Identity
(autonumber) property.

How can I do this in Access 2003.

Reply to this Message

Mr B - 20 Nov 2005 01:49 GMT

Dave,

You cannot override the AutoNumber of Access as you describe. You also
cannot have a zero value in the auto number.

Signature

HTH

Mr B

> I have an Access table with an AutoNumber that I want to have a '0' value.
>
[quoted text clipped - 4 lines]
>
>

Reply to this Message

MacDermott - 20 Nov 2005 01:58 GMT

I beg your pardon!
I am admittedly operating in Access 2000, but I'd be mighty surprised if
this has changed.
I have a table named MyTable, with an AutoNumber field named ID.
Executing this inserts a row with a value of 0 in ID:
INSERT INTO MyTable (ID) VALUES (0)

> Dave,
>
[quoted text clipped - 7 lines]
> >
> > How can I do this in Access 2003.

Reply to this Message

Arvin Meyer [MVP] - 20 Nov 2005 02:56 GMT

> I beg your pardon!
> I am admittedly operating in Access 2000, but I'd be mighty surprised if
> this has changed.
> I have a table named MyTable, with an AutoNumber field named ID.
> Executing this inserts a row with a value of 0 in ID:
> INSERT INTO MyTable (ID) VALUES (0)

Correct, you can insert any long integer into an autonumber field in an
empty Access table if the field is a unique index. The table can have any
value inserted in an autonumber field if it is not a unique index (a rare
occurance, but possible).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Reply to this Message