Retrieve List of Tables in MS Access File

Question

If I can open a connection to an MS Access file in C#, how can I retrieve a list of the different tables that exist in the Access DB (and if possible, any meta-data associated with the tables)?

At the very least, the description of the table (if one is saved) — Yaakov Ellis
– Yaakov Ellis, Commented Nov 10, 2009 at 4:25
possible duplicate of How can I get a list of tables in an Access (Jet) database? — Fionnuala
– Fionnuala, Commented Feb 9, 2012 at 10:55

Yaakov Ellis · Accepted Answer · 2009-11-09 09:19:43Z

39

I just found the following solution from David Hayden

// 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());

answered Nov 9, 2009 at 9:19

Yaakov Ellis

41.7k27 gold badges135 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MindRoasterMir Over a year ago

Thanks. It worked for me. C# WPF Visual Studio 2019

ParmesanCodice · Accepted Answer · 2009-11-09 09:17:04Z

Here's are some links:

Here's a VB.NET snipit to get all the columns of a Access Table, I know it's not exactly what you're looking for, but a similar primciple apples when listing all the tables:

Dim oleConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & myDB & ";User Id=admin;Password=;")

oleConn.Open()
Dim schemaTable As DataTable
Dim i As Integer
schemaTable = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Column s, _
New Object() {Nothing, Nothing, "tblTheTableToListColumns", Nothing})
For i = 0 To schemaTable.Columns.Count - 1
Debug.Print(schemaTable.Rows(i)!COLUMN_NAME.ToStri ng)
Next i
oleConn.Close()

Collectives™ on Stack Overflow

Retrieve List of Tables in MS Access File

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related