CODEWITHSUNDEEP
c#ms-access
namco
namco

Reputation: 6338

How to drop table in Access if exists

I use C# and access db.
There is a statement in mysql for dropping table like below: 

drop table if exists t_table

So do you know the similar statement for Access?

Upvotes: 2

Views: 11247

Answers (1)

mj82
mj82

Reputation: 5263


I don't know about SQL statment like this in Access.
You can, howewer, do one of the following:

  • Try to drope table without checking if exists, catching exception (it should have specific code if no table was found) and ignoring it.

  • Try to check in Access hidden table MSysObjects if table exists (with ADO however, it has no permission by default)

  • Use a code like the one below (bad thing: dropping table do not return records affected):

    using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;data source=c:\myDatabase.mdb"))
{
    conn.Open();

    string tableToDelete = "myTable";   //table name
    bool tableExists = false;

    DataTable dt = conn.GetSchema("tables");

    foreach (DataRow row in dt.Rows)
    {
        if (row["TABLE_NAME"].ToString() == tableToDelete)
        {
            tableExists = true;
            break;
        }
    }

    if (tableExists)
    {
        using (OleDbCommand cmd = new OleDbCommand(string.Format("DROP TABLE {0}", tableToDelete), conn))
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("Table deleted");
        }
    }
    else
        MessageBox.Show(string.Format("Table {0} not exists", tableToDelete));
}

Upvotes: 3

Related Questions