11

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?

Jon B
  • 51,025
  • 31
  • 133
  • 161

4 Answers4

12

Try the GetSchema()

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

    connection.Open();

    DataTable userTables = connection.GetSchema("Tables");
VAShhh
  • 3,494
  • 2
  • 24
  • 37
7

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

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
2

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
Tim Lentine
  • 7,782
  • 5
  • 35
  • 40
  • 1
    just trying it and I get: `Record(s) cannot be read; no read permission on 'MSysObjects'`. – hawbsl May 27 '11 at 13:13
  • @hawbsl 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. – Tim Lentine May 27 '11 at 13:34
  • 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. – David-W-Fenton May 28 '11 at 21:19
  • 1
    @David-W-Fenton 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. – Tim Lentine May 31 '11 at 12:47
  • 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. – David-W-Fenton Jun 02 '11 at 20:07
0

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"]);
                }
            }
stratos lumpereas
  • 33
  • 4