In a datasheet view, until you press enter, it does not submit the insert.

You are able to edit the column in question, then press enter.

In reality, auto increment is just ensuring a primary key for data integrity.

Over time, records are sure to be deleted anyways, so gaps will naturally occur.

Both SQL Server and Access are doing what is expected.

And the message you get initially implies you tried to break the rules of the constraint.