How to check if specific column exists in an Access database table
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?
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"));
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();