Add New Field in MS Access Programmatically using ADOX (C#.Net)

You need to add reference before you start (Refer to my previous post – Rename Field in MS Access Programmatically using ADOX (C#.NET) )


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.let_ActiveConnection(connString)

   AddField(cat, "TargetTable", "Employee_Name_2", DataTypeEnum.adVarWChar, 50, string.Empty);
}

private void AddField(ADOX.CatalogClass cat, string tableName, string newFieldName, DataTypeEnum varType, int size, string defaultValue)
 {
   // #1
   // cat.Tables[tableName].Columns.Append(newFieldName, varType, size);

   ColumnClass col = new ColumnClass();
   col.Name = newFieldName;
   col.Type = varType;
   col.DefinedSize = size;

   // Make this field become nullable field.
   col.Attributes = ColumnAttributesEnum.adColNullable;

   // #2
   cat.Tables[tableName].Columns.Append((object)col, DataTypeEnum.adInteger, 0);
   // #3 - Assign default value after column appended.
   if (!string.IsNullOrEmpty(defaultValue))
   {
      col.Properties["Default"].Value = defaultValue;
   }
}

Issues when using Append() method

Issues when using Append() method

Issues when using Append() method (Refer #1 in source code above):

  1. You only can defines field name, variable type and size for the new field.
  2. New field are not allow NULL when created. (Tested in Access 2003).
  3. You cannot sets default value for new field.

In order to create nullable column with default value,  I use a ColumnClass object – col, and set the necessary properties.

Few things here:

  1. From reference #2, col object need to pass as object type in Append(…) method.
  2. When you pass col object into Append() method, the rest of the arguments in Append(…) actually do NOT overwrite the ‘Type’ and ‘DefinedSize’ properties in col object. So the 2nd argument and 3rd argument is just to fill up the extra places in Append(…).
  3. In reference #3, you only can assign the default value after appended the new field. (Refer to FIX: Cannot Use ADOX to Set Default Value for Column Property)

~~~ [ End ] ~~~

Reference:

  1. Rename Field in MS Access Programmatically using ADOX (C#.NET)
  2. FIX: Cannot Use ADOX to Set Default Value for Column Property

Keyword: ALTER COLUMNS, ALTER TABLE, Microsoft Access

2 responses to “Add New Field in MS Access Programmatically using ADOX (C#.Net)

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s