How to know if a table exists in an Access Database in an OleDb connection

Question

I'm using the below code to connect to an Access Database using OleDb connection in C# .Net

How can I know if the table that I have hard-coded into the program actually exists in the file, so that I can show the user the appropriate message?

try
{
    var dbSource = "Data Source = " + source;
    const string dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";

    using (var con = new OleDbConnection())
    {
        con.ConnectionString = dbProvider + dbSource;
        con.Open();

        using (var cmd = new OleDbCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM [Concrete Design Table 1]";

            // How do I know the table name is valid? It results in errors when it is not?
            // How to prevent it?
            using (var dataReader = cmd.ExecuteReader())
            {
                while (dataReader != null && dataReader.Read())
                {
                    // read the table here
                }
            }
        }
    }
}
catch (Exception e)
{
    MessageBox.Show(e.ToString());
}

You can get the list of tables with `var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });`, if I am understanding what you want correctly. — svinja, Dec 09 '14 at 15:55
@svinja Thanks svinja for the clue, but can you tell me where in schema the list of the names are saved? I couldn't find it. — Vahid, Dec 09 '14 at 15:59

score 5 · Accepted Answer · answered Dec 09 '14 at 16:08

5

You can get the list of tables with

var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

and go through them:

foreach (var row in schema.Rows.OfType<DataRow>())
{
    string tableName = row.ItemArray[2].ToString();
}

or check for existence:

if (schema.Rows
          .OfType<DataRow>()
          .Any(r => r.ItemArray[2].ToString().ToLower() == tablename.ToLower()))
{
    // table exists
}

Ugly, I know. :(

answered Dec 09 '14 at 16:08

svinja

5,495
5
25
43

the strange thing is that the tables exists in my exe folder database but it does not get displayed in the above code! – Hilal Al-Rajhi May 31 '20 at 07:03

score 2 · Answer 2 · answered Nov 22 '16 at 21:44

Here's a working solution I used (in VB.NET, but I guess you can figure out the C# variation):

Dim myTable As Object, TableSchema As Object

Dim con As OleDb.OleDbConnection
con = New System.Data.OleDb.OleDbConnection
con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & myFileName & ";"

If Not con.State = ConnectionState.Open Then con.Open()
TableSchema = con.GetSchema("TABLES")

myTable = TableSchema.select("TABLE_NAME='MYTABLENAME'")
If myTable.length = 0 Then
    Dim cmd As New OleDb.OleDbCommand
    cmd.Connection = con
    cmd.CommandText = "CREATE TABLE MYTABLENAME;"
    nAffected = cmd.ExecuteNonQuery
End If

score 1 · Answer 3 · edited May 23 '17 at 11:55

SQL error will throw OleDbException and you can check SQLState for specified error. If the table does not exists, The code should be 3376 according to Microsoft's documentation. So your code should look like

catch (OleDbException e)
{ 
    switch (dbException.Errors[0].SQLState)
    {
        case "3376":
            MessageBox.Show(dbException.Errors[0].Message); // or any message
            break;
        default:
            MessageBox.Show(e.Message);
    }
}

How to know if a table exists in an Access Database in an OleDb connection

3 Answers3