C# OLE DB: How can I reorder columns of database table by using a dataset?

Question

I know this might be a bit awkward but I am trying to modify the order of certain columns in a MS Access database in C# with OLE DB. How can I commit a certain change in the order of the columns of a datatable in a dataset? If that is not possible, how can I reorder columns of database table by using a dataset?

Here is a sample of what I have (in C#):

command.Connection = conn;
command.CommandText = tableName;
command.CommandType = CommandType.TableDirect;
adapter = new OleDbDataAdapter(command);
dataset = new DataSet(tableName);
adapter.Fill(dataset, tableName);
dataset.Tables[0].Columns[dataset.Tables[0].Columns.Count-1].SetOrdinal(CB_PositionCol.SelectedIndex);
dataset.Tables[0].AcceptChanges();

The AcceptChanges does not seem to work for what I wish to do since I believe it only commits changes in DataRows...

Thank you for any help!

Answer 1

Use an ALTER TABLE statement with an OleDbCommand. DataSet, DataTable and DataTableAdapters are meant to be transparent to the underlying structure of the data. You can actually use Table Adapters to transform data from two different structures.

http://www.functionx.com/vbnet/oledb/Lesson02.htm