Deleting a Table from Database (MS Access)

Question

_{Friday, May 29, 2015 3:47 PM}

In my Access Database, I have two tables - (1) Control; and (2) Results. I have to delete Results in order to build a new "Results" table with different columns. 

For information, the way I access table (1) "Control" is as follows:

 OleDbConnection connection = new OleDbConnection();
 connection.ConnectionString = @"path;";

 OleDbDataAdapter adapter2 = new OleDbDataAdapter("SELECT * FROM Control", connection);
  connection.Open();
  adapter2.Fill(DS1, "Control");

  [Steps to update "Control"

The above works well. No problem. 

Question: How to delete the other table - "Results".  I searched badly on the web with no luck ...

Really appreciate your help. Thank you very much!

Ed

All replies (8)

_{Friday, May 29, 2015 4:56 PM ✅Answered}

You can use drop.

Which would be something like....

string dropCommand = "DROP TABLE Results;";

 OleDBConnection conn = new OleDBConnection("Your connection string");
 conn.Open();
 OleDBCommand cmd = new OldDBCommand(dropCommand, conn);

 cmd.ExecuteNonQuery();
 conn.Close();

Hope that helps.

Technet articles: WPF: Change Tracking; All my Technet Articles

_{Friday, May 29, 2015 9:43 PM ✅Answered}

Just a suggestion -- if you are using Access as a backend for an actual job -- I would stick with just using Access and Access forms, VBA, ... since Access is a self contained RDBMS/Development platform.  If the idea is for multiple users to be able to interface with Access -- that was the ticket for windows 3.11.  Access has since been superseded by sql server like 20 years ago for the multi-user environment.

If you are just experimenting -- you could write some sql like "Drop Table tblx" and run that from an ADO.net command object.

cmd.CommandText = "Drop Table tblx";

cmd.Execute();

If you do step up to sql server, you can forgo ado.net for entity framework -- much cleaner and better performance.  Entity Framework superseded ado.net 4 or 5 years ago.

Also, dropping tables from the client app is not the best practice.  Dropping #temp tables -- from a sql server (like in stored procedures) is a common practice.  But that is not happening at the client -- it happens at the server.

Rich P

_{Friday, May 29, 2015 4:18 PM}

string mySelectQuery = "delete * from results";

OleDbConnection myConnection = new OleDbConnection(@"path;");

OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);

myCommand.Connection.Open();

OleDbDataReader myReader = myCommand.ExecuteNonQuery();

The above code should help you delete all the records from the Results table.

sivanitha

_{Friday, May 29, 2015 5:54 PM}

string mySelectQuery = "delete * from results";

OleDbConnection myConnection = new OleDbConnection(@"path;");

OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);

myCommand.Connection.Open();

OleDbDataReader myReader = myCommand.ExecuteNonQuery();

The above code should help you delete all the records from the Results table.

sivanitha

This will delete records, it doesn't delete/drop table object

Fouad Roumieh

_{Saturday, May 30, 2015 6:16 AM}

Thank you very much! It does work very well!

_{Saturday, May 30, 2015 6:21 AM}

Dear Richard,

Thanks for the tips! Indeed I am making a transition from MS Access to C#. As there are old programs and database developed in MS Access, my first step is to use the database while re-coding in C#. This allows me to compare the results to make sure a smooth transition in language. The next step will then to drop MS Access database and to use SQL (I was told My SQL or what). 

Best regards,

Ed

_{Saturday, May 30, 2015 6:23 AM}

That's useful actually - as I also need that for manipulating the table. Thank you very much!

_{Saturday, May 30, 2015 6:58 AM}

In VB code I would like to get-      My name is "Kumar". in out put.... 

I used label 1 . text = "My name is "Kumar"."  but I failed ...

help me how to get it.