Check for existence of a certain table in access DB using C# OleDB connection

Question

I'm struggling to find a valid answer to what I'm trying to find. Basically, I want to check if for example:

"tableNO1" exists or if every other table exists...

By the way, I'm using Access 2002-2003 if that somehow helps :) Do you think I should upgrade to the latest version?

Background: I'm trying to create run-time buttons that each one of them has a DB table, and when I close my program the tables that I created for each run-time created button will be saved. After I launch the program again I should click a button that will add these buttons that have DB tables (Each one of them has a dedicated table). for example, if 9 run-time buttons were created in the program before - each of them will have a DB table. I will have a max 9 button and each of them will be named tableNO(n) n=number of table when I click the button that creates run-time buttons for the first time, it will create a button called "tableNO1", the second time "tableNO2" will be created, and so on...

Thanks in advance.

Answer 1

Ok, there are several ways to do this, but I suggest this code:

   public Boolean TableExist(string sTable)
    {
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB2))
        {
            conn.Open();
            string[] sRestrict = new string[] {null,null,null,null};
            sRestrict[2] = sTable;
            DataTable MySchema = new DataTable();
            MySchema = conn.GetSchema("Columns",sRestrict);

            return (MySchema.Rows.Count > 0);
        }
    }

The above is also how you can get the schema (table def) as a table.

Thus, say

if (TableExist("tblHotels")
{
     // your code here
}

Now, because you are possible (likely) using a loop, then you might consider for reasons of performance to pass a valid connection to the TableExist function, and thus for your loop not re-create a connection each time - as that will slow things down quite a bit.

Note that "many" often suggest doing this:

SELECT * FROM MSysObjects WHERE [Name] = 'tableNO1' AND Type = 1

The problem with above is by default, the MySysObjects requires elevated rights. The database can be opened, and then using security settings in access the rights to MySysOjbects can be changed - but it more work then the above code/function.