View original (French)

[ACCESS] reset the auto-increment ID

Solved
xdiz -  
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? 
Configuration: Windows XP Firefox 3.0.1

11 answers

  1. FabDev92
    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.
    23
    1. Dodino
      thanks bro
      0
  2. Jean_Jacques
    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.
    3
    1. Christian
      Hello
      It's very simple. You display your table in creation mode, delete the field containing the auto number, and recreate it with the same name. You will be starting from scratch. As a precaution, before doing this, make a backup.
      Christian
      2
      1. Lys
        It doesn't work :-(
        0
    2. almux
      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!

      ;)
      1
      2. xdiz Author
        Hi,

        If that's really the only one, then I'll do what you say ;)

        Thanks, see you!
        0
        1. Jean_Jacques
          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.
          0
          1. xdiz Author
            I too would have liked to have better without wanting to offend you... because your method is a bit patchy ;)

            Let's wait a bit for an ACCESS professional to arrive on the topic :)
            0
            1. mike
              Désolé, je ne peux pas accéder à des liens externes. Veuillez fournir le texte que vous souhaitez traduire.
              0
          2. xdiz Author
            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 ;)
            0
            1. jouan_a
              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
              0
          3. michel_m Contributor
            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
            0
            1. Anonymous user
              Hello!

              To reset the ID of your table, you just need to empty it after saving the content ^^
              phpMyAdmin will record the highest existing ID and use it for future inserts!

              There you go,
              Maxence

              --
              When we grow up, we learn things we wanted to hear when we were little.
              0
              1. almux
                Thank you Maxence,

                Indeed, the reset works perfectly.
                Initially, I had erased the field. But when recreating it, I made two mistakes:
                1) Forgot to uncheck the "place after the first field" (!)
                2) Did not set the correct value (int) (!!)

                Since then, no more problems. ;)

                Almux
                0
            2. Anonymous user
              Sure, happy for you that it's working again ^^

              Feel free to visit my tutorial site, I’m sure you’ll be interested ^^
              http://www.maxmicro.fr/

              Maxence

              --
              When we grow up, we learn things we wanted to hear when we were little.
              0