Get List of Tables in an Access Database - ADO.NET Tutorials
by David Hayden ( ASP.NET C# SQL Server Developer ) Filed: ADO.NET Tutorials
A question came up in the MSDN Forums regarding how to get a list of tables in an Access Database. I have a couple tutorials on how to do this in SQL Server, but I never tackled the challenge using Microsoft Access:
I thought I wrote an example using SQL Server Management Objects ( SMO ), but I guess not. I will have to add that to the list :)
The key is the use of GetSchema, which is new to ADO.NET 2.0. Here is an article I wrote introducing GetSchema:
Here is the code to get a list of tables in an Access Database, short and sweet:
// 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);
}
// Add list of table names to listBox
for (int i=0; i < userTables.Rows.Count; i++)
listBox1.Items.Add(userTables.Rows[i][2].ToString());
The userTables DataTable looks like this using the DataSet Visualizer:
Pretty simple once you get to appreciate GetSchema. Hope this helps.
Written By: David Hayden ( ASP.NET C# SQL Server Developer )
Filed: ADO.NET Tutorials