Equivalent of SET IDENTITY_INSERT OFF in Access

Question

As the title suggests, is there any way of turning off an autonumber field in Access to allow me to insert a row with an id of my choosing, such as you would do with SET IDENTITY_INSERT OFF in SQL Server?

score 7 · Accepted Answer · answered Oct 07 '11 at 18:33

7

You can insert an ID with SQL.

INSERT INTO Table1 (ID) Values (-10)

answered Oct 07 '11 at 18:33

Fionnuala

90,370
7
114
152

You know what, you're absolutely right, I was getting hooked on an error on the insert statement which I presumed the autonumber was causing the problem, however, just tried a simple insert and yes, you can easily add a row, now to go track down the error then!! :-) – Coesy Oct 08 '11 at 20:56

score 4 · Answer 2 · answered Oct 07 '11 at 18:34

The autonumber property sets a field's default value as the autonumber seed value. If the autonumber field is also the primary key (or has a separate unique constraint), you can't re-use any of the stored values. However, you can explicitly insert any long integer value which doesn't conflict with the existing stored values. And without a unique constraint on the autonumber field you can insert the same value repeatedly.

You may not really even need the equivalent of SET IDENTITY_INSERT OFF

Equivalent of SET IDENTITY_INSERT OFF in Access

2 Answers2

Linked