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)"
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)
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?????