How to check if specific column exists in an Access database table

Question

I want to know how to check if a specific column (e.g: date) exists in a specific table(e.g: myTable) in an Access database.

I've read this answer which offers a query which results in another query.

IF NOT EXISTS(SELECT * 
              FROM sys.columns 
              WHERE [name] = N'columnName' 
                AND [object_id] = OBJECT_ID(N'tableName'))
BEGIN
    ALTER TABLE ADD COLUMN MYCOLUMN
END

But what I need is a true/false result.

UPDATE 1

How can I do that in my C# application?

Maybe using SqlCommand.ExecuteScalar() or something else?

Answer 1

As Andre451 mentions in his answer, you can use Access DAO to inspect the Fields collection of the relevant TableDef object, like this:

// test data
string dbFileSpec = @"C:\Users\Public\Database1.accdb";
string tblName = "Clients";
string colName = "LastName";

// COM reference required for project:
// Microsoft Office 14.0 Access Database Engine Object Library
//
var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
Microsoft.Office.Interop.Access.Dao.Database db = dbe.OpenDatabase(dbFileSpec);
Microsoft.Office.Interop.Access.Dao.TableDef tbd = db.TableDefs[tblName];
bool colExists = false;
foreach (Microsoft.Office.Interop.Access.Dao.Field fld in tbd.Fields)
{
    if (fld.Name.Equals(colName, StringComparison.InvariantCultureIgnoreCase))
    {
        colExists = true;
        break;
    }
}
db.Close();

Console.WriteLine("Column " + (colExists ? "exists" : "does not exist"));

Answer 2

Thanks to everyone who offered a solution, gathering up some of the answers, I came up with my own version of solution. Maybe it's not the best solution around, but at least I don't need an extra dll to add to the references or deal with some stored procedures Access won't support.

OleDbConnection con = new OleDbConnection("my database address");
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 1 date FROM myTable";
con.Open();
bool exists = true;
try
{
  var x = cmd.ExecuteScalar();
}
catch (Exception e)
{
  exists = false;
}
con.Close();

Answer 3

Try this:

BEGIN

IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName'))
SELECT 0
ELSE
SELECT 1 
END

To use in c#:

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcedure3";
con.Open();
var x = cmd.ExecuteScalar();
MessageBox.Show(x.ToString());
con.Close();

To use without stored procedure:

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName')) SELECT 0 ELSE SELECT 1 ";
con.Open();
var x = (int)cmd.ExecuteScalar();
MessageBox.Show(x.ToString());
con.Close();

Answer 4

In case anyone requires this in the future, below is the method I use on my Access databases.

void CheckTableFields(OleDbConnection con)
{
    var table = "Company";
    var field = "Location";
    var sSQL = $"SELECT TOP 1 * FROM [{table}]";

    using (OleDbConnection con = new OleDbConnection("MsJetOledbConnectionString"))
    {
        con.Open();
        using (var cmd = new OleDbCommand(sSQL, con))
        {
            try
            {
                DataTable dtGen = new DataTable();
                dtGen.Load(cmd.ExecuteReader());

                System.Data.DataColumnCollection columns = dtGen.Columns;

                if (!columns.Contains(field))
                {
                    //Field NOT found - add new 'Location2' column
                    cmd.CommandText = $"ALTER TABLE [{table}] ADD COLUMN {field} TEXT(50)";
                    var result = cmd.ExecuteNonQuery();

                    WriteLine($"{DateTime.Now}:   Table Column {{{field}}} added successfully");
                }
                else
                {
                    WriteLine($"{DateTime.Now}:   Table Column {{{field}}}  already exists");
                }
            }
            catch (Exception ex)
            {
                WriteLine($"[DataService.CheckCompanyTableFields]  Error - {{{ex.Message}}}");
            }
        }
    }
}

Answer 5

In Access VBA you could use the TableDef.Fields collection. Don't know if you can use these objects from c#.

Why don't you simply do (pseudocode)

columnExists = True
try
    ExecuteSql "SELECT TOP 1 [Date] FROM myTable"
catch
    // Error: column doesn't exist
    columnExists = False

Answer 6

Simple by:

select true

Complete Code:

IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName'))
    BEGIN
        select true
    END

ELSE

    BEGIN
        select false
    END