CODEWITHSUNDEEP
c#asp.netms-access
goofyui
goofyui

Reputation: 3492

From C#, How to validate whether Table is already exists in MS Access DB

From C#, I am opening MS Access DB & importing values to Access Table. We need to validate whether Table is already exists in MS Access DB ??

 access.OpenCurrentDatabase(sMedExpressAccessDB, true, null);

 // Drop the existing table data

 access.DoCmd.DeleteObject(Access.AcObjectType.acTable, "drug");
 // Run the saved import
 access.DoCmd.RunSavedImportExport("MedExpressODBC");

 // Close the database
 access.CloseCurrentDatabase();

Upvotes: 0

Views: 1271

Answers (3)

Sahil Verma
Sahil Verma

Reputation: 33

This can b helpfull in ms access using c#. Tested

protected void Button1_Click(object sender, EventArgs e)
{
    string DB = Server.MapPath("App_Data/OnlineTestEngine.mdb");
    con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + DB);

    string query = "Select * from current_test";
    con.Open();
    cmd1 = new OleDbCommand(query, con);
    try
    {
        dr = cmd1.ExecuteReader();
        if (dr.GetName(0).ToString() == "Qno")
        {
            Label1.Text = "Table Already Exist";
            dr.Close();
            con.Close();
            con.Open();
            string q = "Drop table current_test";
            OleDbCommand cmd2 = new OleDbCommand(q, con);
            cmd2.ExecuteNonQuery();
            Label1.Text += " | Table Deleted from Database";
        }
    }
    catch(Exception ex)
    {
        Label1.Text = "Table Not Exist";
        string query2 = "create table current_test(Qno counter, Questions varchar(255), ans1 varchar(255), ans2 varchar(255), ans3 varchar(255), ans4 varchar(255), right_ans varchar(255), marks int, response int, PRIMARY KEY (Qno))";
        cmd3 = new OleDbCommand(query2, con);
        con.Close();
        con.Open();
        dr = cmd3.ExecuteReader();
        Label1.Text += " | Table Created Sucessfully";

    }
    finally
    {
        con.Close();
    }
}

Upvotes: 0

reiniero
reiniero

Reputation: 428

Chok, try using a try...catch construct around your delete command to ignore the error when you delete an object that's not there.

If you really need to find out if a table is there, as you're using .Net, I'd suggest using the GetOleDbSchemaTable method for OleDbConnections, see e.g. here: TekTips post by Headwinds with this vb code:

Dim schemaDT As DataTable
Dim thisNull As System.DBNull()
schemaDT = thisConn.GetOleDbSchemaTable( _
OleDbSchemaGuid.Tables, thisNull)

As the referenced post mentions:

The DataTable that is returned from this statement is the collection of information about tables The advantage is that this approach will work (with slight modifications) for other ADO.Net databases as well. For more info, see the MSDN documentation.

Alternatives: As people have said, you can use the msysobjects system table in Access, but that table is not guaranteed to have the same info in various Access versions.

Also, you could use the DAO Tabledefs collection, or its ADO equivalent.

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52645

One way is to use the msysobjects table. E.g.

SELECT COUNT(*) FROM msysobjects where name = 'foo' and type = 1

You also use the DAO tableDef object. If I recall correctly this can be access via the Runtime callable wrapper for DAO if you wanted.

Upvotes: 3

Related Questions