ADOX Rearrange Or Insert Columns Rather than Append them in Access Vb6, VB.Net or CSharp

Question

I need to insert a field in the middle of current fields of a database table. I'm currently doing this in VB6 but may get the green light to do this in .net. Anyway I'm wondering since Access gives you the ability to "insert" fields in the table is there a way to do this in ADOX? If I had to I could step back and use DAO, but not sure how to do it there either.

If yor're wondering why I want to do this applications database has changed over time and I'm being asked to create Upgrade program for some of the installations with older versions.

Any help would be great.

Answer 1

This should not be necessary. Use the correct list of fields in your queries to retrieve them in the required order.

BUT, if you really need to do that, the only way i know is to create a new table with the fields in the required order, read the data from the old table into the new one, delete the old table and rename the new table as the old one.

Answer 2

I hear you: in Access the order of the fields is important.

If you need a comprehensive way to work with ADOX, your go to place is Allen Browne's website. I have used it to from my novice to pro in handling Access database changes. Here it is: www.AllenBrowne.com. Go to Access Tips then scroll down to ADOX Code.

That is also where I normally refer people with doubts about capabilities of Access as a database :)

In your case, you will juggle through creating a new table with the new field in the right position, copying data to the new table, applying properties to the fields, deleting original table, renaming the new table to the required (original) name.

That is the correct order. Do not apply field properties before copying the data. Some indexes and key properties may not be applied when the fields already have data.

Over time, I have automated this so I just run an application to do detect and implement the required changes for me. But that took A LOT of work-weeks.