Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Changing a filed from long to autonumber (2003)

    I had a corrupted record in a table. I changed the Autonumber field to Long Integer and this allowed me to delete the record. However when I tried to change it back into an Autonumber (I know you can't do this in design view) using DDE I keep getting this "Invalid field data type error.
    I've done this before and it worked, I've looked and re - read till I'm blue in the face but I can't see where I am going wrong.

    ALTER TABLE tblCat ALTER COLUMN CategoryID COUNTER (8113,1);

    this is the MS knowledge base article I am using for reference -
    http://support.microsoft.com/kb/202117/EN-US/

    I've had to leave out some of the records to get the file small enough to attach

    PS - great to see the lounge back on line, such and invaluable resource.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a filed from long to autonumber (2003)

    Copy the structure of your table to a new empty table.
    Modify the field type of CategoryID to AutoNumber in the new table.
    Create an append query to append all the records of the old table in the new table.
    Delete the old table.
    Rename the new table with the old Name.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing a filed from long to autonumber (2003

    I was laboring under the misapprehension that the existing values from the ID field would be lost if I did this and new autonumbers would be assigned.

    So I tried it and guess what - the existing numbers in the autonumber field are retained. But you knew that.

    thank you very much.

Posting Permissions

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