MS Access Oledb column properties

Question

And I solved this problem in VBA using DAO here

Using the Oledb framework, I created a function that can look up a record value. However it only gets the raw value. I need to find the value of the column property called "Row Source"

Can someone explain to me how to find the foreign key value using Oledb

Below is my function for a traditional look up query.

string IDatabase.LookupRecord(string column, string table, string lookupColumn, string lookUpValue)
{
    OleDbCommand cmdLookupColumnValue = new OleDbCommand();

    string sqlQuery = "SELECT [" + table + "].[" + column + "] " +
                      "FROM [" + table + "] " +
                      "WHERE [" + table + "].[" + lookupColumn + "] = '" + lookUpValue + "'";

    cmdLookupColumnValue.CommandText = sqlQuery;
    cmdLookupColumnValue.CommandType = CommandType.Text;
    cmdLookupColumnValue.Connection = connection;

    string result = "";

    try
    {
        result = cmdLookupColumnValue.ExecuteScalar().ToString();
    }
    catch(Exception ex)
    {
        MessageBox.Show("Query is not valid :" + ex.ToString());
    }

    return result;
}

EDIT I found the following code here Its the closest Ive gotten so far. It does get column properties like the column Description but it doesnt work for row Source. Any Ideas?

public void Test()
    {
        string columnName = "Main Space Category";

        ADOX.Catalog cat = new ADOX.Catalog();
        ADODB.Connection conn = new ADODB.Connection();
        conn.Open(connectionString, null, null, 0);
        cat.ActiveConnection = conn;
        ADOX.Table mhs = cat.Tables["FI - ROOM"];

        var columnDescription = mhs.Columns[columnName].Properties["Description"].Value.ToString();

        MessageBox.Show(columnDescription);

        conn.Close();               
    }

Answer 1

I strongly suspect that the RowSource property of a table column is so specific to Access that you will have to use DAO to retrieve it. The following C# code is an example of how you might do that:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace daoConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string TableName = "Cars";
            string FieldName = "CarType";

            // This code requires the following COM reference in your 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(@"Z:\_xfer\Database1.accdb");
            try
            {
                Microsoft.Office.Interop.Access.Dao.Field fld = db.TableDefs[TableName].Fields[FieldName];
                string RowSource = "";
                try
                {
                    RowSource = fld.Properties["RowSource"].Value;
                }
                catch
                {
                    // do nothing - RowSource will remain an empty string
                }

                if (RowSource.Length == 0)
                {
                    Console.WriteLine("The field is not a lookup field.");
                }
                else
                {
                    Console.WriteLine(RowSource);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}