Rename Field in MS Access Programmatically using ADOX (C#.NET)

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:

  1. ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability (Just search for ‘ADOX’ in this article to check for ADOX usage).
  2. 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

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,


using ADOX;

// 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#.

cat.let_ActiveConnection(connString);

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)

Reference:

  1. Microsoft Office Access – SQL Reference
  2. ADO.NET Programmer’s Reference – Chapter 16 – COM Interoperability
  3. How to rename the column name in MS Access table
  4. http://support.microsoft.com/kb/309047

Keyword:  ALTER COLUMNS, ALTER TABLE, Microsoft Access

Advertisements

4 responses to “Rename Field in MS Access Programmatically using ADOX (C#.NET)

  1. Pingback: Add New Field in MS Access Programmatically using ADOX (C#.Net) « 大脚板日记

  2. I am gettting error while executing “cat.Tables[tableName].Columns[originalFieldName].Name = newFieldName;”

    Error = Item cannot be found in the collection corresponding to the requested name or ordinal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s