MS Access Oledb列属性 [英] MS Access Oledb column properties

问题描述

然后我使用DAO 此处在VBA中解决了此问题>

And I solved this problem in VBA using DAO here

使用Oledb框架，我创建了一个可以查询记录值的函数.但是，它仅获得原始值.我需要找到名为行源"的列属性的值

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"

有人可以向我解释如何使用Oledb查找外键值

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;
}

编辑，我在此处找到了以下代码到目前为止最接近的Ive.它确实获得了列属性，例如列说明，但不适用于行源.有任何想法吗?

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();               
    }

推荐答案

我强烈怀疑表列的RowSource属性是Access所特有的，您将不得不使用DAO来检索它.以下C#代码是如何执行此操作的示例:

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());
            }
        }
    }
}

这篇关于MS Access Oledb列属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！