August 21, 2013 at 7:49 am
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.
August 21, 2013 at 7:58 am
I don't think you can do that in Access. If you are using an autonumber why does it matter if there is a gap anyway? This is perfectly normal because when rows get deleted it leaves a gap.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 8:52 am
Sean is absolutely correct - there is no easy way to do that in Access, and in general you shouldn't need to. Access treats autonumber fields as something the user cannot edit. The theory is that their only purpose is to ensure uniqueness.
That said, if you really have to do that, you can copy the autonumber contents into a new Long Integer field that is not an autonumber. Then you delete the autonumber field from your table, add the record that you want and assign it the missing number in the sequence, and finally change the design of the new field to be an autonumber. Then rename the field to be the same as the field you deleted.
If you do that very often you will decide it's a real pain. Alternatives - never allow a record to be deleted, and use a logical delete instead, or move your tables (and some of your fundamental queries) to a SQL Server back-end.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
January 3, 2017 at 10:40 am
The following worked for me:
Get users out of system
Backup Access (back-end if split) database
Open (back-end) Access database and go to Relationships.
Document any relationships (and rules) for the subject table
Remove relationships
Run command in VBA window:
CurrentDb.Execute "ALTER TABLE [Project Table] ALTER COLUMN ProjectNumber COUNTER(20170001,1)"
where 20170001 will ne the NEXT value of auto-number.
Replace Relationships!
I did not experiment with trying to just modify relationships or using a finer scalpel..
Hope it helps someone.
November 12, 2020 at 8:06 pm
I beg to differ. Sometimes there are valid reasons.
Lets say we want to migrate a Table in Access to SharePoint (SP). When a Table is migrated to SP as a List, it loses it any Auto-numbering Fields and Imports as a Number. To maintain some degree of Data Integrity, we need to replicate the Autonumber Field in proper and exact sequence to use the ID Column that is created in the List. If there are any gaps, there are issues.
As Jay Johnson mentioned, you can insert a dummy record into a Autonumber Column, providing it does not violate Referential Integrity, using the Access INSERT Action Query, and that you have enough of the required fields included. I realize this can be a monstrous task, but if begin with your top most table, you do not need to destroy your relationships.
November 13, 2020 at 3:01 pm
First, I should note that you are responding to a thread that from 7 years ago, with the most recent post nearly 4 years ago. Second, as far as I know, there isn't anything in the rules of referential integrity that says you must have a consecutive set of digits. Finally, I think you will find SQL Server is a more robust backend to SharePoint. And that comes from an Access guy.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 4, 2021 at 1:48 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply