CODEWITHSUNDEEP
sqlms-access
Matthew
Matthew

Reputation: 4339

Can you insert a custom value in an Auto-incrementing field in MS Access

The column auto-increments. User wants to insert a row with a specific number (I've confirmed it doesn't already exist in the column). In SQL Server I'd do a quick SET IDENTITY_INSERT ON and insert statement. Is there a way to this for Microsoft Access or are they just out of luck.

Upvotes: 0

Views: 504

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

NotAn answer, but a workaround. Don't make the column autoincrement, but set the default value to max current values plus one. The defualt will, in effect, step up by one, but the user can pick a different value. Has not been tested on very large tables.

Upvotes: 0

June7
June7

Reputation: 21370

Can run an INSERT action SQL.

But then need to Compact & Repair to reset the autonumber seed. Or, per @4dmonster comment, run another INSERT with the maximum autonumber value already in database. As long as the autonumber is set for unique index, the insert will fail but sequence will be fixed and normal data entry will generate next number. I tested and it worked.

I can only guess this autonumber is used as a sequential number that must be accounted for, as in a license number series. Otherwise, sequence gaps should not be a concern and can be ignored.

Upvotes: 2

Related Questions