How can I get a list of tables in an Access (Jet) database?

Question

I need to see if a table exists in an Access database used by my c# program. Is know there are SQL commands for other databases that will return a list of tables. Is there such a command for Access/Jet databases?

Internationalist

Answer 1

Try the GetSchema()

    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\access.mdb";    

    connection.Open();

    DataTable userTables = connection.GetSchema("Tables");

Artiste

So many big fancy answers when this was all I wanted! Thanks!! – Machmeter

Answer 2

Full code : Get List of Tables in an Access Database - ADO.NET Tutorials

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

here is answer for you : http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/d2eaf851-fc06-49a1-b7bd-bca76669783e

Whistler

Answer 3

Something like this should do the trick. The clause Type = 1 specifies tables. Note that this will also include the system tables in the result set (they start with the prefix "MSys".

SELECT Name FROM MSysObjects WHERE Type = 1

Wuhsien

just trying it and I get: Record(s) cannot be read; no read permission on 'MSysObjects'. – Veraveracious

@Veraveracious It sounds like you might need to modify permissions within the Access Database. Tools Menu -> Security -> User and Group Permissions. Assign 'Read Data' permission to the Admin user on the MSysObjects table. – Wuhsien

Changing permissions like that would perhaps violate user-level security that has been set up on purpose. The solution is not to change the permissions on the underlying object (which would make it wide open to anybody), but to use a username/password that has the permissions you need. – Blanka

@Blanka my understanding is that the "MSys" objects in an Access database default to not having permissions granted to even the Admin user, especially for a remote connection. I agree that if this project used the built in Access security features AND this was going to be a long standing query \ process that using a dedicated account with the appropriate permission would be best. I got the sense, however, that this might be a down and dirty type of query request in which case adding another user account w permissions would be overkill. – Wuhsien

If you don't have the permissions to read the system tables with the default admin account, you're not going to have the permission to do anything else. Likely the wrong workgroup file is being used and that's the cause of the read failure on the system tables. – Blanka

Answer 4

that one worked for me

using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                DataTable dt = con.GetSchema("Tables");
                var selectNames = dt.Rows.Cast<DataRow>().Where(c => !c["TABLE_NAME"].ToString().Contains("MSys")).ToArray();
                foreach (var item in selectNames)
                {
                     // add names to comboBox
                    comboBox1.Items.Add(item["TABLE_NAME"]);
                }
            }

Footlambert