GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, ...) always returning zero rows access database

Question

When querying an Access 2000 database, using:

schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, New Object() {Nothing, Nothing, tableName})

Where cn is a valid and open connection, schemaTable always contains zero rows, despite the tableName specified having many indexes.

This documentation, here http://msdn.microsoft.com/en-us/library/cc668764.aspx suggests that MS Access provides this information.

What gives?

Answer 1

It appears that when retrieving .Indexes the third member of the restrictions array corresponds to the Index name, not the Table name. So to retrieve the indexes for a given table it looks like we need to retrieve all of the indexes (no restrictions) and then filter out the ones we don't want.

The following C# code works for me:

using (OleDbConnection con = new OleDbConnection())
{
    con.ConnectionString = myConnectionString;
    con.Open();
    object[] restrictions = new object[3];
    System.Data.DataTable table = con.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, restrictions);

    // Display the contents of the table.
    foreach (System.Data.DataRow row in table.Rows)
    {
        string tableName = row[2].ToString();
        if (tableName == "Clients")
        {
            foreach (System.Data.DataColumn col in table.Columns)
            {
                Console.WriteLine("{0} = {1}",
                  col.ColumnName, row[col]);
            }
            Console.WriteLine("============================");
        }
    }
    con.Close();
}