Finding Tables in MS Access using C#

Question

Is it possible to determine the tables and its column in MS Access in a particular database using c#?

Can any one guide me in this issue?

Answer 1

Apologies, SQL Server biased and posted that answer without actually reading your question properly!

Here is a solution for reading the Table names from an MS Access database. It is the same idea i.e. querying the Db schema.

Snippet

String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();  
Console.WriteLine("Made the connection to the database");

Console.WriteLine("Information for each table contains:");
DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});

foreach(DataRow tableRow in tables.Rows)
{
    Console.WriteLine("Table Name: {0}", tableRow[0]);
    DataTable cols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[]{null,null, tableRow[0], null});
    foreach (DataRow colRow in cols.Rows)
    {
        Console.WriteLine("Column Name: {0}", colRow[0]);
    }
}

con.Close();

Answer 2

Depending on the way you are connecting to the ms-access fiele, you can read tables and column/field names in different ways:

1. With an OLEDB connecion: you can use the ADOX Objects collection and read tables, columns, indexes, etc available in the database

2. With an ODBC connection, you can list the MS-ACCESS system tables, open a recordset on each table, and then read all field names. Tables can be listed with the following SQL instruction:

   SELECT name FROM MSysObjects WHERE type = 1

3. In all case, once a table is opened through a recordset, you can read its field names by looping on the fields collection of the recordset object