Determining if a field is a calculated field in MS Access

Question

I have an Access 2013 database and I'm trying to connect to it in C# and get information about the fields in the database. Everything is working except one thing I'm not sure about is how to determine if a field is a calculated field or not. Here is my Access database schema:

.---------------------------------------------------.
| TestTable1                                        |
|---------------------------------------------------|
| Field Name      | Data Type | Comments            |
|---------------------------------------------------|
| ID              | AutoNumber| Primary Key         |
| Field1ShortText | Short Text|                     |
| Field2LongText  | Long Text |                     |
| Field3Calculated| Calculated| Formula = 1+2       |
.---------------------------------------------------.

Here is my C# code:

using (var connection = new OleDbConnection(ConnectionString))
{
    connection.Open();

    using (DataTable columns = connect.GetSchema("Columns"))
    {
        foreach (DataRow row in columns.Rows)
        {
            if (((string)row["TABLE_NAME"]).ToLower() != tableName.ToLower())
                continue;

            var field = new Field();
            field.FieldName = (string) row["COLUMN_NAME"];
            field.IsCalculated = ? // Here is where I'm stuck
        }
    }
}

I'm iterating through all the columns in my Access database and I am setting a property called IsCalculated. This is a bool property that should be either true if its calculated or false. I'm not sure how to determine this. There is a column returned from GetSchema called COLUMN_FLAGS, which probably has the information I need. However, I cannot seem to find any reference documentation on what these "flags" are.

Any help would be appreciated!

Answer 1

I suspect that if you want a definitive indication of a calculated field you will need to use Access DAO and inspect the Expression property of the Field2 object in the TableDef for the table in question. For example:

// This code requires the following COM reference in your project:
//
//     Microsoft Office 14.0 Access Database Engine Object Library
//
// and the declaration
//
//     using Microsoft.Office.Interop.Access.Dao;
//
// at the top of the class file            

var dbe = new DBEngine();
Database db = dbe.OpenDatabase(@"C:\Users\Public\Database1.accdb");
foreach (Field2 fld in db.TableDefs["TestTable1"].Fields)
{
    if (fld.Expression.Length > 0)
    {
        Console.WriteLine("Field [{0}] is calculated.", fld.Name);
    }
}
db.Close();

Or, as suggested by another answer, you could create an OleDbDataReader object and look at the DataTable returned by the OleDbDataReader.GetSchemaTable method. The OleDb version does not include an IsExpression column but you might be able to infer the "calculated" status by looking for columns where IsReadOnly is true.