Can't Rename ColumnName in and mdb file

[MontgomeryBurns]

I have an mdb file (MS Access). I want to rename a table's column.
I tried this and no exception is thrown, nor it works.

			try
			{
				string szMDBFilePath ="c:\\temp\\db1.mdb";
				string password ="";

				this.m_oleDBConnection.Close();
				

				strTable = "Tabella1";
				strOldColumnName = "campo1";
				strNewColumnName = "newcolname";

				this.m_oleDBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + szMDBFilePath + ";Jet OLEDB:Database Password=" + password + ";";
				m_oleDBConnection.Open();
				
				m_oleDBDataAdapter = new OleDbDataAdapter("SELECT * FROM [" + strTable + "]", m_oleDBConnection);

				m_DBDataSet = new DataSet();
				this.m_oleDBDataAdapter.Fill(m_DBDataSet, "[" + strTable + "]");

				System.Data.DataTableCollection dtcConnessioneATabella = m_DBDataSet.Tables;
				DataTable dtTabella = dtcConnessioneATabella["[" + strTable + "]"];

				

				this.m_oleDBDataAdapter.FillSchema(dtTabella, SchemaType.Source);//m_DBDataSet, "[" + strTable + "]");

				dtTabella.Columns[strOldColumnName].ColumnName = strNewColumnName;

				this.m_oleDBDataAdapter.Update(dtTabella);

				return NO_ERROR;
			}
			catch (Exception e)
			{
				m_strExceptionMessage = e.Message;

				return ERROR;
			}

How am I supposed to implement it?

[torrud]

You can't rename a column name in this way. You have to add a new column with the new name. Then you have to copy the data from the old column to the new one. Finally you have to delete the old column.

[MontgomeryBurns]

There ought to be a way to rename it!
I can do what you say, but I want to rename it. I found someone that did it, using ADO, but I can't see why can't I do it using OleDB.

[Shuja Ali]

OleDB does not provide you any method that renames a column. However, using old ADOX technology you can rename a column in the database.

Look at this thread
http://www.devnewsgroups.net/group/m...opic50261.aspx

[MontgomeryBurns]

it seems strange to me that I can change DataTable.Columns[..].ColumnName property without throwing any exceptions (I can see in the debugger's watch window that the property changes correctly).
The m_oleDBDataAdapter.Update(..) does not throw any exception either.
Are you 100% sure that OleDB does not allow to do it?
I wouldn't like to include other dlls to do it with ADOX....

[Shuja Ali]

DataTable is in memory representation of the database. You can change the names of the columns in the datatable, however that will not reflect in the database itself. Did you check if the column name in the database got changed or not?

[cjard]

it seems strange to me that I can change DataTable.Columns[..].ColumnName property without throwing any exceptions

You dont seem to have realised that a DataTable is a client side data storage container, completely independent of a database. You can call the column names anything you like, they dont ahve to match the db, they dont have to be the same type.. infact, you dont even need to have a db.

DataTable is NOT a database, not THE database, and the fact that the wizard names all the columns like it finds in the database is purely for reasons of a) ease for Microsoft to code it taht way and b) you to recognise what local column represents what remote column

[MontgomeryBurns]

ok with that cjard, I do realize that.
But why OleDbDataAdapter.Update() does not throw exceptions, either? That should interact with the real db, shouldn't it?