Getting table names from .mdb

benglish72

Hi, I'm trying to find a way to get the names of tables in a .mdb file programmatically. The OleDBDataAdapter requires that you use an SQL select statement, but that's a bit hard if you don't yet know the table names in a file. Any help, tips? Thanks, Brian.

Heath Stewart

This has been asked several times in this forum. To note, it's usually a good idea to try searching for previous threads in a forum like this. See my previous response at http://www.codeproject.com/script/comments/forums.asp?msg=776353&forumid=1649#xx776353xx[^] for a discussion of this. Simply, use a query like so:

SELECT Name
FROM MSysObjects
WHERE Type = 1 AND NOT Name LIKE 'MSys%'

Microsoft MVP, Visual C# My Articles

benglish72

Hi, here's a code snippet of what I'm trying to do, but it throws an exception when I try to fill the dataset. I'm not familiar enough with .NET database stuff to know if this is the right way to go about it. Any ideas? Thanks. DataSet myDataSet = new DataSet(); string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ dataFile; string strAccessSelect = "SELECT Name FROM MSysObjects WHERE Type = 1 AND Name LIKE 'MSys%'"; try { OleDbDataAdapter myDataAdapter = new OleDbDataAdapter (strAccessSelect,strAccessConn); myDataAdapter.Fill(myDataSet,"MSysObjects"); DataTableCollection dta = myDataSet.Tables; Int32 iCount = dta.Count; for(Int32 i=0;i

Heath Stewart

First of all, you want WHERE Type = 1 AND **NOT** Name LIKE 'MSys%', as I wrote in my reply to your original post. Second of all, don't waste time and resources with a DataSet when you only need a single column anyway:

using (OleDbConnection conn = new OleDbConnection(strAccessConn))
{
using (OleDbCommand cmd = conn.CreateCommand())
{
// Make sure to modify the WHERE clause
cmd.CommandText = strAccessSelect;

conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
cbTables.Items.Add(reader.GetString(0));

conn.Close();
}
}

This is much more efficient - using a forward-only data reader instead of creating a DataSet schema and filling it, which is good for disconnected data, but terribly inefficient for something like this.

Microsoft MVP, Visual C# My Articles