current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not able to insert value of my own.

This link shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

Now how the same can be done for Access database.If i delete a row having some autonumber,then in future i want to insert same autonumber value.But i am not able to use SET IDENTITY_INSERT for access. So how the same can be done in Access.

share|improve this question
    
stackoverflow.com/a/7691439/1699210 –  bummi Aug 21 '13 at 14:00
    
I saw that link before posting it.But i didnt understood what that answer is. For me its not inserting. Even in sql we cant insert directly for an identity column.Only by using set identity_insert on i can insert in sql server.Don't know how to do it in access –  IT researcher Aug 21 '13 at 14:06
    
I have no problem in Access 2003 and 2007 to add any value via insert sql, even if there are foreign keys defined to the Autonumber column. An update per SQL on the field however is not possible. –  bummi Aug 21 '13 at 14:16
2  
YOu should under no circumstance want to reuse autonumber values. THat completely defeats the purpose of using thema nd it creates risks inteh database. If you don;t want gaps (and a legal reuirement for no gaps is really the only reason that is acceptable here) then you need to roll your own number scheme and not use an autonumber fuield. HOwever, even then, it is a BAD idea that almost 100% will result in data integrity issues or cofusion. It is really really a bad idea. –  HLGEM Aug 21 '13 at 15:14
    
And you should not ever be doing this in SQL server either. Identitiy insert is only for the use when you mneed to migrate records. Using it for this purpose is bery bad. –  HLGEM Aug 21 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.