Announcement

Collapse
No announcement yet.

mcsdatabase - reset numbering

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    mcsdatabase - reset numbering

    How do I reset the database sample ID number in MS access to restart at 1. My ID numbers are up to 2531976! It is a bit cumbersome to work with these big numbers.

    Steve Q
    HomeSeer Version: HS3 Pro Edition 3.0.0.368, Operating System: Microsoft Windows 10 - Home, Number of Devices: 373, Number of Events: 666, Enabled Plug-Ins
    2.0.83.0: BLRF, 2.0.10.0: BLUSBUIRT, 3.0.0.75: HSTouch Server, 3.0.0.58: mcsXap, 3.0.0.11: NetCAM, 3.0.0.36: X10, 3.0.1.25: Z-Wave,Alexa,HomeKit

    #2
    I have read about it on the web somewhere and if I recall it was not that obvious/easy so I never did it. What I suggest is that you make a new table and import all the data from the old one into the new one. That will restart the numbering.

    Comment


      #3
      Michael,

      I found this http://support.microsoft.com/kb/812718
      I used method 2 for Access version 2003. It worked on a practice file, but it did not work on the backup copy of my database. The procedure is very complicated and I am not seeing the same steps. I am not very familiar with Access and I don't really know what I'm doing! I may try again later.

      I tried your suggestion to import the data into a new table but the I could not get rid of the ID numbers. I've got a macro that deletes data periodically. I need to import that also. Its frustrating.. I use Excel extensively. I know it extremely well. But Access is a whole different animal!!

      Steve Q
      HomeSeer Version: HS3 Pro Edition 3.0.0.368, Operating System: Microsoft Windows 10 - Home, Number of Devices: 373, Number of Events: 666, Enabled Plug-Ins
      2.0.83.0: BLRF, 2.0.10.0: BLUSBUIRT, 3.0.0.75: HSTouch Server, 3.0.0.58: mcsXap, 3.0.0.11: NetCAM, 3.0.0.36: X10, 3.0.1.25: Z-Wave,Alexa,HomeKit

      Comment


        #4
        I figured out how to renumber the database. Here are detailed instructions based on the ones from Microsoft:
        =======================================================
        To reset an AutoNumber field value by using Method 2, follow these steps:

        In Access 2003 and in earlier versions:

        1. Make a note of the AutoNumber field name and the name of the table whose autonumbers will be reset.
        2. Open the table window and delete the column containing the AutoNumber field from the table you want to renumber. Yes to both pop-up boxes.
        3. Switch back to the Database window. Make a copy of the table you just changed (without the autonumbers) by RIGHT-cliking on the table name then select copy. In the left pane of the database window, RIGHT-click on tables and select paste. A popup window will ask for a name and paste options. Name the table "renumtable" and select "Structure Only". This table will now appear on the database list of tables.
        3. In the Database window click Queries in the left pane. Double Click Create query in Design view on right pane.
        4. In the Show Table dialog box, select the Table to be renumbered (main table). Click Add and then click Close.
        5. To select the fields, double-click on all the fields appearing in the small sub-window (do not click on ID if it appears). They will be added in the order they are clicked. Do this for all the fields except for the AutoNumber field.
        6. On the Main menu, click Query, Append Query. The Append window will open.
        7. From the Table Name drop down list, select "renumtable". Click OK. The Append Query window will now show the field names to append.
        8. On the Main Menu, click Query then click Run.
        9. A dialog box appears with the text that follows: You are about to append # row(s). Click Yes to add the rows. (Do not click run more than once even though nothing appears to have happened).
        10. Close the Append Query window; click X. Click No to save changes to query.
        11. In the database window, click Tables on the left pane. RIGHT-click the renumbered table and then click Design View. The design window will open.
        12. In the Design view for the table, add a field to the bottom of the list with the same field name (ID) that you deleted in step 1. Tab to the Data Type column, select "auto Number" from the drop down list. Now close (X) and save the table.
        13. Open the table that is now renumbered by double clicking on "renumtable". The ID (autonumber) column will be added to the right side of the table. Drag the column to the left side of the table.
        14. Save the "renumb" table.
        15. Close any other tables that might be open.
        16. From the Database window, select the original table to be renumbered (single click). RIGHT-click and delete this original table (the one with the ID column deleted).
        17. Select "renumb" table (single click). RIGHT-click, choose RENAME and give it the name of the table you just deleted.
        18. Close the database.

        ========================================================

        It's difficult but it works.

        Steve Q
        HomeSeer Version: HS3 Pro Edition 3.0.0.368, Operating System: Microsoft Windows 10 - Home, Number of Devices: 373, Number of Events: 666, Enabled Plug-Ins
        2.0.83.0: BLRF, 2.0.10.0: BLUSBUIRT, 3.0.0.75: HSTouch Server, 3.0.0.58: mcsXap, 3.0.0.11: NetCAM, 3.0.0.36: X10, 3.0.1.25: Z-Wave,Alexa,HomeKit

        Comment

        Working...
        X