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

score 1 · Answer 1 · answered Dec 15 '17 at 00:47

You are in the good direction For each table, you can call again to GetOleDbSchemaTable, but in this case, with different parameters:

    DataTable schemaColumns =
        connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
        new object[] { null, "MyTableName", null, null });

whith OleDbSchemaGuid you can specify which schema retrieve, and with the filters, specify the table schema

In this link, you can find information about how filter, for exmaple by table name to retrieve columns info. Restrictions are an array and you should put the filter value in its corresponding position

score 1 · Accepted Answer · answered Dec 15 '17 at 01:20

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

Get tables/schema from access 97 database

2 Answers2