[ACCESS] reset the auto-increment ID
Solved
xdiz
Posted messages
109
Registration date
Status
Member
Last intervention
-
Dodino -
Dodino -
Good evening,
After testing my table, wanting my id to reset to zero in order to make the table "clean," I performed a DELETE * FROM myTable and also tried TRUNCATE TABLE myTable, but the truncate function does not work in ACCESS.
How can I reset an auto-increment id to zero after deleting ALL the tuples from the table?
After testing my table, wanting my id to reset to zero in order to make the table "clean," I performed a DELETE * FROM myTable and also tried TRUNCATE TABLE myTable, but the truncate function does not work in ACCESS.
How can I reset an auto-increment id to zero after deleting ALL the tuples from the table?
Configuration: Windows XP Firefox 3.0.1
11 answers
Hello,
I have a solution that is not through SQL query, but a feature of ACCESS:
When the table has been emptied of its tuples, in the "Tools" menu, you can use "Database Utilities" -> "Compact Database"
=> This resets the auto-increment field counter to 0.
CAUTION: this can be dangerous if the table still contains tuples, as the auto-increment field starts again from 0 and may eventually collide with a value that has already been used, which would be a problem if this same field is part of the primary key.
I have a solution that is not through SQL query, but a feature of ACCESS:
When the table has been emptied of its tuples, in the "Tools" menu, you can use "Database Utilities" -> "Compact Database"
=> This resets the auto-increment field counter to 0.
CAUTION: this can be dangerous if the table still contains tuples, as the auto-increment field starts again from 0 and may eventually collide with a value that has already been used, which would be a problem if this same field is part of the primary key.
Dodino
thanks bro
Hello,
I only know one trick to get the desired result.
- Select the relevant table
- ctrl+C then ctrl+V
- Assign an arbitrary name
- Choose the structure only option
You just have to replace the old one with the newly created table
It works well as a workaround
Have a good day
Science only discovers what has existed since forever.
REEVES Hubert.
I only know one trick to get the desired result.
- Select the relevant table
- ctrl+C then ctrl+V
- Assign an arbitrary name
- Choose the structure only option
You just have to replace the old one with the newly created table
It works well as a workaround
Have a good day
Science only discovers what has existed since forever.
REEVES Hubert.
Hello,
I attempted the operation... Now, I need to set an auto-increment field in the first position of the table... But I can't do it!
It's refusing to set the auto-increment:
Error
SQL query:
ALTER TABLE `contactlivre_1` CHANGE `ref_com` `ref_com` VARCHAR( 8 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL AUTO_INCREMENT
MySQL responded:
#1063 - Incorrect column specifier for column 'ref_com'
What should I do?
HELP!!!!! Aaaaaaaargl!
SOLUTION: I SHOULD NOT HAVE SET VARCHAR... but INT !!!!!!
Phew!
;)
I attempted the operation... Now, I need to set an auto-increment field in the first position of the table... But I can't do it!
It's refusing to set the auto-increment:
Error
SQL query:
ALTER TABLE `contactlivre_1` CHANGE `ref_com` `ref_com` VARCHAR( 8 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL AUTO_INCREMENT
MySQL responded:
#1063 - Incorrect column specifier for column 'ref_com'
What should I do?
HELP!!!!! Aaaaaaaargl!
SOLUTION: I SHOULD NOT HAVE SET VARCHAR... but INT !!!!!!
Phew!
;)
Xdiz,
I’m surprised that no other contributor has suggested anything better...
A more... let's say "structured" method would have really pleased me.
In the meantime, my solution must be considered the best.
Actually, I’m somewhat disappointed. To be continued...
In any case, thank you for the feedback.
See you...
Science only finds what has existed since always.
REEVES Hubert.
I’m surprised that no other contributor has suggested anything better...
A more... let's say "structured" method would have really pleased me.
In the meantime, my solution must be considered the best.
Actually, I’m somewhat disappointed. To be continued...
In any case, thank you for the feedback.
See you...
Science only finds what has existed since always.
REEVES Hubert.
Hi Fab, I did it too, but there are no SQL solutions to solve this issue for Access.
In the end, we can only compact or copy/paste the table structures.
Thanks anyway ;)
In the end, we can only compact or copy/paste the table structures.
Thanks anyway ;)
Yop,
Well, I might have a simpler way
to reset your auto-increment to zero in SQL
you do:
INSERT INTO your_table (column1, column2, ..., auto-incr) VALUES (value1, value2, ..., 0)
you might need to put in multiple values if you have mandatory fields
auto-incr is the auto-increment field that you have, you set it to 0
then you do
DELETE FROM your_table WHERE auto-incr = 0
and that way your first record is at 1
Well, I might have a simpler way
to reset your auto-increment to zero in SQL
you do:
INSERT INTO your_table (column1, column2, ..., auto-incr) VALUES (value1, value2, ..., 0)
you might need to put in multiple values if you have mandatory fields
auto-incr is the auto-increment field that you have, you set it to 0
then you do
DELETE FROM your_table WHERE auto-incr = 0
and that way your first record is at 1
Hello,
To compact the database, you need to check the JRO library in VBE tools-ref Microsoft Jet and Replication Object .x library
Sub raz_autoincrement()
Dim je as new JRO.jetengine
Je.compactdatabase "Path and name of the database to clean", datasource "path and name of a temporary database"
kill "Path and name of the database to clean"
Name "path and name of a temporary database" as "Path and name of the database to clean"
end sub
In "plain"?
We compact the database under a temporary name
We delete the original database
We rename the temporary database to the original database name
Do I need to mention that it's more than wise to make a copy of the database before anything else and to practice first with dummy databases
This procedure consumes a lot of adrenaline for the programmer...
As such, the manual method described by Fab is the most sensible, especially when it comes to cleaning up after testing (thus a unique procedure)
--
Best regards,
Michel
To compact the database, you need to check the JRO library in VBE tools-ref Microsoft Jet and Replication Object .x library
Sub raz_autoincrement()
Dim je as new JRO.jetengine
Je.compactdatabase "Path and name of the database to clean", datasource "path and name of a temporary database"
kill "Path and name of the database to clean"
Name "path and name of a temporary database" as "Path and name of the database to clean"
end sub
In "plain"?
We compact the database under a temporary name
We delete the original database
We rename the temporary database to the original database name
Do I need to mention that it's more than wise to make a copy of the database before anything else and to practice first with dummy databases
This procedure consumes a lot of adrenaline for the programmer...
As such, the manual method described by Fab is the most sensible, especially when it comes to cleaning up after testing (thus a unique procedure)
--
Best regards,
Michel