ADO.NET 3.5 Cookbook, 2nd Edition

11.13. Listing Tables in an Access Database

Problem

You need a list of all tables in your Access database.

Solution

Use the GetOLEDBSchemaTable() method of the OleDbConnection class or ActiveX Database Objects Extensions for DDL and Security (ADOX).

The first approach uses the GetOLEDBSchemaTable() method to return schema information about user tables. These results are then displayed.

The C# code in Program.cs in the project ListAccessDatabaseTables is demonstrates the first approach and is shown in Example 11-15.

Example 11-15. File: Program.cs for ListAccessDatabaseTables solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ListAccessDatabaseTables
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                @"C:\Northwind 2007.accdb;";

            OleDbConnection connection = new OleDbConnection(oledbConnectString);
            connection.Open(  );

            // Retrieve schema information for all tables.
            DataTable schemaTable =
                connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                new object[] {null, null, null, "TABLE"});

            Console.WriteLine("---Tables, using GetOleDbSchemaTable(  )---");
            foreach (DataRow row in schemaTable.Rows)
                Console.WriteLine("{0}", row["TABLE_NAME"]);

            connection.Close(  );

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in Figure 11-14.

Figure 11-14. Output for ...