C# code to add a column to an existing MS Access Table.

Revised  post to correct the horrible code formatting.  Sorry....

public static void AddDAOTableColumn(string MDBfile, string tableName, string ColumnName, TypeCode dataType, Int32? columnSize, bool AutoNumber)

{

try

{

EnterProc(System.Reflection.MethodBase.GetCurrentMethod());

dao.DBEngine DBE = new dao.DBEngine();

dao.Database DB = DBE.OpenDatabase(MDBfile, false, false, "");

LogValue(string.Format("Opened Database: {0}", DB.Name));

dao.TableDef table = DB.TableDefs[tableName];

LogValue(string.Format("Found Table: {0}", tableName));

bool bAddColumn = true;

if (table != null)

{

foreach (dao.Field fld in table.Fields)

{

if (fld.Name == ColumnName)

{

bAddColumn = false;

break;

}

}

}

else

bAddColumn = false;

LogValue(string.Format("Table.{0} exists?:{1}", ColumnName, !bAddColumn));

if (bAddColumn)

{

dao.DataTypeEnum columnType;

switch (dataType)

{

case TypeCode.Boolean:

columnType = dao.DataTypeEnum.dbBoolean;

break;

case TypeCode.DateTime:

columnType = dao.DataTypeEnum.dbDate;

break;

case TypeCode.Int16:

columnType = dao.DataTypeEnum.dbInteger;                            

break;

case TypeCode.Int32:

columnType = dao.DataTypeEnum.dbLong;                            

break;

case TypeCode.Object:

columnType = dao.DataTypeEnum.dbMemo;

columnSize = null;

break;

case TypeCode.String:

if (columnSize <= 255)

columnType = dao.DataTypeEnum.dbText;

else

{

columnType = dao.DataTypeEnum.dbMemo;

columnSize = null;

}

break;

default:

columnType = dao.DataTypeEnum.dbText;

if (columnSize == null)

columnSize = 50;

break;

}

dao.Field newfield = new dao.Field();

newfield.Name = ColumnName;

newfield.Type = (short)columnType;

if (newfield.Type == (short) dao.DataTypeEnum.dbText)

newfield.AllowZeroLength = true;

if (columnSize != null)

newfield.Size = Convert.ToInt32(columnSize);

if (AutoNumber)

newfield.Attributes = (int)dao.FieldAttributeEnum.dbAutoIncrField;

table.Fields.Append(newfield);

table.Fields.Refresh();

DB.TableDefs.Refresh();

LogValue(string.Format("Created Column: {0}", newfield.Name));

DB.Close();

table = null;

newfield = null;

DB = null;

DBE = null;

}

ExitProc(System.Reflection.MethodBase.GetCurrentMethod());

}

catch (Exception ex)

{

ErrorLog(ex);

}

}

Updating MS Access Linked Tables - A better solution

I added DAO 3.6 reference to my project and used the following code

Decide to go this route because MS Jet 4.0/DAO 3.6 are part of the operating system and are in Windows 2000, XP, Vista and Windows 7. They are updated by Windows Update and the security patches are applied as appropriate.

        public static void RelinkDAOTables(string MDBfile, string filepath, string sql)
        {
            DataTable linkedTables = TableFromMDB(MDBfile, sql);

            dao.DBEngine DBE =new dao.DBEngine();
            dao.Database DB = DBE.OpenDatabase(MDBfile, false, false, "");
            foreach (DataRow row in linkedTables.Rows)
            {
                dao.TableDef table = DB.TableDefs[row["Name"].ToString()];
                table.Connect = string.Format(";DATABASE={0}{1} ;TABLE={2}", filepath, row["database"],row["LinkedName"]);
                table.RefreshLink();
            }      
           
        }