MS Access is a lightweight database and lack of some database feature, for example it does not support the SQL query to rename the field. Therefore we need to workaround to rename the field in MS Access.
MS Access SQL reference: Microsoft Office Access – SQL Reference
Instead I never had VB6 in my PC but only .NET, so I looking for workaround in .NET. Below are the references:
- ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability (Just search for ‘ADOX’ in this article to check for ADOX usage).
- How to rename the column name in MS Access table (Reference in VB6, Main source for me to convert ADOX into .NET)
You need to add the ADOX COM reference into your .NET project because we are going to use ADOX to do the trick.
Add ADOX COM Reference from Solution Explorer
First you need to to go to Add Reference from Solution Explorer, add Microsoft ADO Ext.2.8 for DDL and Security from COM tab.
Then, use the code below,
// Skipped namespace and public class here...
private void btnUpdate_Click(object sender, EventArgs e)
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb;Persist Security Info=False";
CatalogClass cat = new CatalogClass();
// cat.ActiveConnection = connString <-- Error here
// Cannot assign to cat.ActiveConnection directly.
// Use code at below to assign connection string in C#.
RenameField(cat, "TargetTable", "Staff", "Employee");
private void RenameField(CatalogClass cat, string tableName, string originalFieldName, string newFieldName)
cat.Tables[tableName].Columns[originalFieldName].Name = newFieldName;
Run the button btnUpdate_Click to make the trick work, so the field Staff will rename to Employee.
Note that you cannot assign the connection string directly to cat.ActiveConnection since it is not a string object, and I guess it only accept Connection object from ADO. Use cat.let_ActiveConnection(connString) instead.
Micorsoft example on ADOX connection string – PRB: Unhandled Exception When You Set ADO Property to a String in Visual C# .NET
~~~ End ~~~
Upcoming article: Add New Field in MS Access Programmatically using ADOX (C#.Net)
- Microsoft Office Access – SQL Reference
- ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability
- How to rename the column name in MS Access table
Keyword: ALTER COLUMNS, ALTER TABLE, Microsoft Access