In some applications, we want to delete all the data from all the tables. And in almost all cases of database development, you test the application with dummy data or enter a few dummy values for reports, etc. What happens when the database has to be taken at client's site? I have seen many people deleting the contents of each table manually!! Well, we are smart workers, this does not suit us. I have developed small code that clears all tables for me. Here is the source code for you.

Please note that we will use two COM components in this application (see the beauty of interoperability):

• ADODB
• ADOX

Microsoft ActiveX Data Objects 2.7 Library
Microsoft ADO Ext. 2.8 for DDL and Security

... The Code ...

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button2.Click
    Dim con As New ADODB.Connection()
    Dim cat As New ADOX.Catalog()
    con.Open("provider=microsoft.jet.oledb.4.0; data source=" & TextBox1.Text)
    cat.ActiveConnection = con
    Dim tbl As ADOX.Table
    For Each tbl In cat.Tables
        If tbl.Type = "TABLE" Then con.Execute("delete from [" & tbl.Name & "]")
    Next
    tbl = Nothing
    cat = Nothing
    con.Close() : con = Nothing
    MessageBox.Show("All tables cleared successfully!!", _
   "Congratulations", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

... Objects and Methods Used ...

The objects used in the above code are:

• Connection: To connect to the database
  • Open Method: To open the connection
  • Execute Method: To execute a query on database
• Catalog: To access all the objects of database
  • Tables Collecton: holds all the tables of the catalog (database) [each table as a table object in the collection]
  • Type Property (Table Object): helps us distinguish between user and system tables and objects
  • Name Property (Table Object): returns name of the table

The code is self explanatory, however, if you find any difficulty in understanding the code, please read the chapter discussing ADO in my Visual Basic COM Introduction tutorial (ADODB). However, if you still have any doubts, please feel free to contact me at rahul@coder000.com

If you feel the need to discuss anything about the code or anything in the source code (as it has more contents than discussed in the above paragraphs), please do not hesitate in contacting me at http://www.coder000.com. Your feedback is welcome... :)