Alex Rivera | Logout

How to extract the schema of an Access (.mdb) database?

Asked 2009-03-30T20:10:01.047
33

I am trying to extract the schema of an .mdb database, so that I can recreate the database elsewhere.

How can I pull off something like this?

Edit
Report

2 Answers

9

The following C# outlines how to obtain the schema from a .mdb file.

Obtain a connection to the database:

String f = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "database.mdb";
OleDbConnection databaseConnection = new OleDbConnection(f);
databaseConnection.Open();

Get the name of each table:

DataTable dataTable = databaseConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int numTables = dataTable.Rows.Count;
for (int tableIndex = 0; tableIndex < numTables; ++tableIndex)
{
    String tableName = dataTable.Rows[tableIndex]["TABLE_NAME"].ToString();

Get the fields for each table:

    DataTable schemaTable = databaseConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
    foreach (DataRow row in schemaTable.Rows)
    {
        String fieldName = row["COLUMN_NAME"].ToString(); //3
        String fieldType = row["DATA_TYPE"].ToString(); // 11
        String fieldDescription = row["DESCRIPTION"].ToString(); //27
    }
}

Where do the 3, 11 and 27 come from? I found them by inspecting DataRow.ItemArray with a debugger, does anyone know the 'correct' way?


Here is the screenshot ProVega alludes to in the comments of this answer:

a list of numbers with descriptions next to them

answered 2009-06-11T11:44:44.153
2

Check out the docmd.TransferDatabase command. It is probably your best bet for build integration that needs to replicate the data structure

answered 2009-03-30T21:38:18.497

Your Answer