How to get list of Tables in MS Access using C# code

Many time you want to access your tables dynamically means as required you access the table. Rather than opening the MS Access application and then open the database check the tables.

Here I'll show you how to get the table names from the Access Database.

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/App_Data/PhraseContentFiles/" + filename + "") + ";User Id=admin;Password=;";

            OleDbConnection connection = new OleDbConnection(ConnectionString);
            connection.Open();
            System.Data.DataTable dt = null;

            // Get the data table containing the schema
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

// Get the data table containing the schema
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
These few lines of code can give you all system tables schema now if you want to have only tables created by the user then you have to put a filter on data of your DataTable dt.

foreach (DataRow row in dt.Rows)
            {
string strSheetTableName = row["TABLE_NAME"].ToString();
if (row["TABLE_TYPE"].ToString()=="TABLE")
                    ddlTableName.Items.Add(strTableName);
                i++;
            }

Following is the schema of the Access Table that contains information about the all tables in the Access Database.