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!

Does the answer here (https://social.msdn.microsoft.com/Forums/en-US/c8d7b3a6-cd99-4a45-a90b-66bfd6142b35/get-column-name-and-data-types-of-access-tables?forum=adodotnetdataproviders) help at all? I am not an access dev, but this was something I stumbled across. My thought is, if the field type is "calculated" then you could set your bool based on that. However, I don't know enough to post as an answer. — Tommy, Feb 10 '16 at 23:57
@Tommy - The problem is, the DATA_TYPE returned by `GetSchema` isn't returning `Calculated`. It's returning `Integer`, which my calculated answer is an integer, so that makes sense. However, it isn't helping me figure out if its truly calculated or just an ordinary integer field. — Icemanind, Feb 11 '16 at 00:02
Fair enough - I wondered if it would but as I said, I couldn't quickly test and say yes or no since Access is not really "my jam". Interesting question btw. — Tommy, Feb 11 '16 at 00:03

Gord Thompson · Accepted Answer · 2016-02-11T03:48:14.923

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.

There is no IsReadOnly property in any of the properties of OleDb, so I had to include access interop "just" to get this info. Anyway, we can't do much just to adapt to what we have. — Dejan Dozet, Jul 05 '22 at 09:27

score 0 · Answer 2 · edited Feb 11 '16 at 00:18

0

You'll want to use the GetSchemaTable() method. The property you should be looking for is IsExpression. See the MSDN for SqlDataReader.GetschemaTable()

edited Feb 11 '16 at 00:18

Tommy

39,592
10
90
121

answered Feb 11 '16 at 00:14

Tah

1,526
14
22

2

Sorry, but `OleDbDataReader#GetSchemaTable()` does not have an `IsExpression` column, so your answer applies to MS SQL Server but not MS Access. – Gord Thompson Feb 11 '16 at 02:27

Determining if a field is a calculated field in MS Access

2 Answers2