Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: ALTER TABLE ADD column problem

    When the below VB6 code is executed, the added column "smDOBIRS" does not show up at the end of the table as expected. It is placed in between two existing columns and becomes the 3rd from last column.

    This exact column was added to the exact same table name in two other databases with no problem. The other two databases contain similar, but not exactly the same data.

    Set cmdAlterTable = New ADODB.Command
    Set cmdAlterTable.ActiveConnection = cnn1
    cmdAlterTable.CommandText = "ALTER TABLE Suspect ADD smDOBIRS varchar(10)"

    cmdAlterTable.Execute

    Thank you for any help you can provide.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your question is unusual given that normally people are asking how to insert columns between others and not have them appear at the end!

    A relational database management system materialises relations as tables. One of the principles of a relation is that there is no intrinsic order to its attributes (columns).
    http://en.wikipedia.org/wiki/Relation_%28database%29

    In short - you should not care about the order of columns. If you do then you should create views (queries) to enforce your required order.

    That's the theory - it doesn't answer your question of course but is it satisfactory?

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Thank you for your answer. The reason I'm concerned about the order of the columns is that there are two separate databases (for Audit and Archive) where data is entered and extracted using indexes. Sample code:


    For L = 0 To rsSARmaster.Fields.Count - 1
    rsSARmasterArchive.Fields(L) = rsSARmaster.Fields(L)
    Next L

    So it is important to have the columns in the same order and this update has to be done for about 100 clients.

    I had a client send me their database and the ADD column worked fine, placing the new column at the end. I deleted the table from the problem database and imported a copy of the table from the clients DB. After doing that it worked fine.

    Even though I tried a "Repair" which did not help, I have to assume that somehow the table in my test database was corrupt?????

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jbkzmiller View Post
    I have to assume that somehow the table in my test database was corrupt?????
    I don't see any reason to assume that.

    Another option would be to use an object model to make the alterations - DAO or ADOX.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jbkzmiller View Post
    For L = 0 To rsSARmaster.Fields.Count - 1
    rsSARmasterArchive.Fields(L) = rsSARmaster.Fields(L)
    Next L
    Looking here you could also employ a little defensive programming (now you are forewarned about this):
    Code:
    For L = 0 To rsSARmaster.Fields.Count - 1
        rsSARmasterArchive.Fields(rsSARmaster.Fields(L).name).Value = rsSARmaster.Fields(L).Value
    Next L
    Your code no longer relies on column order.

  6. #6
    Join Date
    Aug 2007
    Posts
    3
    Great advice. Thanks.

Posting Permissions

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