Clear all database tables – VB DDL

This is a simple example demonstrating how to use SQL Data Definition Language (DDL) commands in vb.net. In a now distant past, I used to need a code to clear all the tables in a MS Access database. This code is particularly quite useful during the testing phase when the database becomes fat with ‘dummy’ data.

To accomplish this task we need two COM components:

• Microsoft ActiveX Data Objects 2.7 Library (ADODB)
• Microsoft ADO Ext. 2.8 for DDL and Security (ADOX)

To add the reference to these libraries, go to Project | Add Reference | COM and select the above components. Do you see the how easy it is to use COM in .Net? Some people like to call this COM Interoperability.

Dim con As New ADODB.Connection()
Dim cat As New ADOX.Catalog()
con.Open("provider=microsoft.jet.oledb.4.0; data source=" & "<database.mdb>")
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

The objects & methods 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

If you need more input on COM and the mechanics behind it, go ahead and read the post VB COM Introduction, discussing ADO as an example.