Get tables/schema from access 97 database

Question

I have an Access 97 database that I am trying to get the data schema and the data out of. I don't know how many tables there are, or what they're called, nor what column names there are.

I have no problem getting into the database programmatically, but how do you discover the schema?

I'm using this to get the schema table:

static DataTable GetSchemaTable(string connectionString)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable schemaTable =
            connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
            new object[] { null, null, null, "TABLE" });
        return schemaTable;
    }
}

Answer 1

GetOleDbSchemaTable with OleDbSchemaGuid.Tables field returns tables (including view names) defined inside catalog, while the object array refers to this construction:

new object { "table_catalog", "table_schema", "table_name", "table_type" }

OleDbSchemaGuid consists of 3 fields: OleDbSchemaGuid.Tables, OleDbSchemaGuid.Columns and OleDbSchemaGuid.Primary_Keys. To get a table properties, you can use OleDbSchemaGuid.Columns field:

connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
        new object[] { "table_catalog", "table_schema", "table_name", "column_name" });

Since you want to find table schema, set the second argument and leave another arguments as null value:

var columns = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
        new object[] { null, "schema_name", null, null });

If you want to get properties by both table schema & table name, use third argument too:

var columns = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
        new object[] { null, "schema_name", "table_name", null });