Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: adding an auto number pk to an existing table

    I have a table with 14,000 recs. There is no primary key on it. I was thinking of :
    1. copying the table,
    2. deleting all records,
    3. adding an auto number primary key in design view
    4. using an append query to add records,
    5. add the relationships and delete the existing table,
    6. finally renaming the copied table to the original.

    Would this be a good work around? ,, else can i have as many suggestions as possible


    Tnks

    M~

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: adding an auto number pk to an existing table

    Originally posted by mikezcg
    I have a table with 14,000 recs. There is no primary key on it. I was thinking of :
    1. copying the table,
    2. deleting all records,
    3. adding an auto number primary key in design view
    4. using an append query to add records,
    5. add the relationships and delete the existing table,
    6. finally renaming the copied table to the original.

    Would this be a good work around? ,, else can i have as many suggestions as possible


    Tnks

    M~
    Try modifying the tablestructure in designview and simply add a autonumber-field.
    You can do this by selecting the first line (= first field) and rightclick:
    select insert.

    I tried it with a small table and it worked.

  3. #3
    Join Date
    Oct 2003
    Posts
    311

    Re: adding an auto number pk to an existing table

    Yes you can insert a colm but when u save the table you get errors and it will not update the table. this didnt work.

  4. #4
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    You sure you did it correctly - just done it on a table with 70K plus records - worked fine.

  5. #5
    Join Date
    Oct 2003
    Posts
    311

    Angry

    I just tried again.

    I went to the 1st col in design view.
    Right clicked
    inserted a column
    set the name to PK and data type to AutoNumber
    Clicked on save :


    MSG : Could not update; Currently locked
    MSG : Errors were encountered durign the save operation.
    Fields were not added. Properties were not updated.


    I know there are no other users on this db. and i dont have any tables forms or queries open

  6. #6
    Join Date
    Oct 2003
    Posts
    311
    I exported the table to a new DB then it workd !!! Thks Guys!

Posting Permissions

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