How to list all Access table names?

Question

Hi, I’m wanting to display the table names that are inside the Access file and play for a variable. How can I do this? Does the C# platform itself provide attributes for me to do this? I’m using the reference System.Data.Oledb.

Below is the code I’m trying to get the tables, but it doesn’t work.

 string path = "./info.mdb";
 OleDbConnection conn = new OleDbConnection(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", path));    
 conn.Open();
 OleDbCommand odc = new OleDbCommand("SELECT * FROM " + tabela, conn);
 OleDbDataReader reader = odc.ExecuteReader();
 var tabelas = reader.GetSchemaTable();

Above in the table variable, I can’t find the list of tables that is in the file info.mdb.

Anyone can help?

Answer 1

The method Getschema which lists the tables (and other MDB structures) that you should use is a Connection method and not a Reader method. The getSchemaTable of Reader serves to read the schema of the table defined in the query

Here is an example of how to read all tables from an MDB

// Microsoft Access provider factory
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DataTable userTables = null;
using (DbConnection connection = factory.CreateConnection()) {
  // c:\test\test.mdb
  connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test\\test.mdb";
  // We only want user tables, not system tables
  string[] restrictions = new string[4];
  restrictions[3] = "Table";

  connection.Open();

  // Get list of user tables
  userTables = connection.GetSchema("Tables", restrictions);
}

List<string> tableNames = new List<string>();
for (int i=0; i < userTables.Rows.Count; i++)
    tableNames.Add(userTables.Rows[i][2].ToString());

Note:

The Restrictions is an array that functions as a filter. If nothing is passed on it, all the structures (tables, views, filters, presses, etc.) will be returned. Since Oledb is a generic Provider (it serves both for MDB and XLS, DBF, etc.) Restrictions will depend on the base. I leave here the unofficial translation of the documentation:

The restrictionValues parameter can provide n depth of values which are specified in the restriction collection for a collection specific. To define values in a given constraint and not define the values of the other restrictions, you need to define the previous restrictions to null and then set the value suitable for the constraint you want to specify a value for.

An example of this is the collection of "Tables". If the collection "Tables" has three restrictions (database, owner and table name), and you want to get only the tables associated with the owner "Carl", you must pass the following values (at least): null, "Carl". If a constraint value is not passed, the default values are used for this constraint. This is the same mapping how to pass null, which is different from passing an empty string for the value of the parameter. In this case, the empty string ("") is considered the value for the specified parameter.