Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Change to Autonumber (A97 SR-1)

    I currently have an arbitrary (but unique) number field (primary key) in one table that relates it to corresponding records in a related table. However, my data source is now abandoning the use of this unique identifier, and I would like to convert this field to an AutoNumber field in the primary table AND have the corresponding number field in all records in the related table updated to match. Any suggestions on how to go about this?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Change to Autonumber (A97 SR-1)

    Backup your database. Delete the relationship between the 2 tables. Rename the parent table. In Database Window, click on New table, then Import. Select the same database, and the newly renamed table. Under options, select "definitions only". In this newly imported table, change the datatype of the ID field to autonumber, then save as original tablename. A simple append query using old tablename as source will add all information from old table to new table.

    In child table, change datatype of ID field to number (long integer) if not already. Then re-establish the relationship between tables.

    And did I mention to backup the database before starting!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change to Autonumber (A97 SR-1)

    Hi Mark,

    Thanks for the advice; however, something isn't working right. I can create a duplicate table with an AutoNumber field in place of my previous field (called "Number" which was a text field with a type of serial number in it). I then run an append query, and I have a new table with an AutoNumber field instead of the previous "Number" field. (Why AutoNumber starts at 37 instead of 1 I don't understand.) Anwyay, I end up with a new table autonumbered from 37 on up. BUT, trying to simply change the datatype of the same "Number" field in my related table doesn't work. I can't reestablish the relationship because the child table is related to my primary table by the original Number field which I no longer have in my new primary table. I must be missing something here.

    Perhaps I can't do what I want to. It seems to me that I should probably add an Autonumber field to my existing primary table. Then I need a similar long integer ID field added to my child table. At that point I need to copy the AutoNumber field from my primary table to the ID field in my child table wherever the original "Number" fields match. I can then establish the relationship using the new ID numbers and remove the older "Number" fields from each table.

    Does this make sense?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change to Autonumber (A97 SR-1)

    You can't add autonumber fields to tables that already contain data. Think about it, there would be no way to populate the field. What are you trying to append into the autonumber field if your previous Number field was a text serial number anyhow?

    Actually, what you described is close to the way I would do it. However, I would append all your records into a new table with the same structure as the old but with an additional Autonumber field as the new primary key. Once you copy the table structure to a new table (copy structure only, please), your autonumbers should start at 1. That will give you a table you can join to your existing tables on the old key. Create new tables to hold copies of the others the same way--keep all the old fields but add a new field for the new foreign key. Once you've copied all of the existing tables to the new structure, you can archive and/or delete the original tables. Then you can create new relationships between the tables and remove the old ones before you delete the old key fields.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change to Autonumber (A97 SR-1)

    Hi Charlotte,

    I'm not sure why you thought an AutoNumber field couldn't be added to a table that already had data because it certainly worked for me, and it started with record number one and incremented from there. Anyway, I managed to update my related table and copy the AutoNumber field from my primary table into the corresponding ID field. I then could delete the old relationship and the number fields that went with it, and set up the new relationship using the AutoNumber fields. It finally all worked, but I had to go into a lot of queries and reports and make appropriate changes.

    One thing I ran into that puzzled me was when I went to delete the old Number field in my table. It was the Primary Key and an error message popped up saying I couldn't delete a Primary Key when the table was related to another table. So, I deleted all relatinships, BUT I kept getting the same message. Access somehow didn't recognize that the relationships had been deleted. I got around this by creating a new data base and importing the tables from my original one. It then no longer "thought" that the tables were still related, and it let me delete the old Primary Key and assign it to the new AutoNumber field.

    Thanks for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change to Autonumber (A97 SR-1)

    I thought you already *had* an autonumber in that table? Anyhow, did you go into the relationships window and delete the relationships? You can't just remove the tables, you have to click on the joins and remove them first.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Change to Autonumber (A97 SR-1)

    My method was based on the assumption you wanted to just make the existing field an autonumber. This would make your life easier, as you wouldn't have to change any other coding (such as listboxes, etc.) to refer to the "new" autonumber field vs. the old field. This is why I'd advise not changing it's name.

    When you did the append query, did you make sure the newly changed field was updated from the old field? You want the existing records to keep the value for this field, but then autonumber would pickup on any new records.

    The method you suggested would work. However, as I mentioned above, consider the implications throughout your code of changing the PrimaryKey field for your tables. If you do go ahead with this, make sure you delete the old field (or at least change its name), so that any unknown use of the field will produce an error.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change to Autonumber (A97 SR-1)

    I didn't already have the Autonumber field in the table, and yes I did delete each of the relationships -- however, Access doggedly insisted that relationships were still there until I imported the tables into a new database.

    Thanks to everyone for the help. I did manage to convert over to using Autonumber as my linking field between tables. Frankly, I should have done that in the first place instead of using the serial numbers assigned by others. I'm slowly learning more and more about Access with the help of you and fellow Loungers. This is undoubtedly the best and most responsive forum I have encountered on the net.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •