0

I'm working on a program to quickly check all tables in a .mdb file to ensure that none of its primary key properties have been altered. I'm having difficulty figuring out how to check the field properties of the primary keys, however.

I've managed to get all of the primary keys and their schema using OleDbSchemaTable. However, the properties that it lists are not exactly what I'm looking for. 

foreach (string table in tablenames)
{

    System.Console.WriteLine("*****" + table + "*****");

    DataTable schemaTable = (cnn as OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
                                       new Object[] { null, null, table});

    int columnOrdinalForName = schemaTable.Columns["COLUMN_NAME"].Ordinal;

    foreach (DataRow r in schemaTable.Rows)
    {

        string keyName = r.ItemArray[columnOrdinalForName].ToString();

        System.Console.WriteLine("\t" + keyName);

        foreach (DataColumn myProperty in mySchema.Columns)
        {

            Console.WriteLine(myProperty.ColumnName + " = " + r[myProperty].ToString());
        }
        Console.WriteLine();
    }

This is the information I want to get:

*****TABLE_NAME*****

PRIMARY_KEY_NAME

Field Size = Long Integer

New Values = Increment

Format =

Caption =

Indexed = Yes (Duplicates OK)

Text Align = General

instead of this:

*****TABLE_NAME*****

PRIMARY_KEY_NAME

TABLE_CATALOG =

TABLE_SCHEMA =

TABLE_NAME = TABLE_NAME

COLUMN_NAME = PRIMARY_KEY_NAME

COLUMN_GUID =

COLUMN_PROPID =

ORDINAL = 2

PK_NAME = PrimaryKey

This is the information I'm trying to reach: https://i.stack.imgur.com/NHsrM.jpg

sfowler12
  • 35
  • 1
  • 1
  • 6
  • 2
    I am not sure you can get that info using OleDb. OleDb is a generic provider that using a database specific code, tries to generalize the access code to the database. It shouldn't have anything specific to a particular database. Instead, perhaps you could get something more using DAO https://stackoverflow.com/questions/18534970/ms-access-oledb-column-properties – Steve Jun 25 '19 at 21:04
  • Thanks for the suggestion @Steve! I'm trying to implement the solution you provided, but am having some issues with getting DAO up and running on my machine. I've added the assembly reference Microsoft.Office.Interop.Access.Dao, but I'm still getting "COMException was unhandled" errors when trying to run it. I'm using Visual Studio 2013 and Microsoft Access 2013. Any ideas? – sfowler12 Jun 27 '19 at 14:47

0 Answers0