delete table if exists in Microsoft Access

Question

I have one application in which I need to delete table if exit in Microsoft Access Database. I saw the code here. The table name which I want to delete is data_table and the access database file name is local_entry so where I need to change the code so it work for my application.

public void testDropTable () throws SQLException{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");
    Statement stmt = con.createStatement();
    ResultSet checkTable = con.getMetaData().getTables(null, null, "POI", null);
    String tableName = null;
    while (checkTable.next())
    {
        System.out.println("In here");
        tableName = checkTable.getString("TABLE_NAME");
        System.out.println(tableName);
    }
    if (tableName != null){
        try {
            String dropTable = "DROP TABLE ";
            String[] tables = DB_TABLE;
            for (int i = 0; i < tables.length; i++){
                String stringCode = new String();
                stringCode = stringCode + tables[i];
                System.out.println(dropTable + tables[i]);

                // Drop each table in the array.
                int temp = stmt.executeUpdate(dropTable + tables[i]);
            }
        }
        catch (Exception e) {
            System.err.println("Exception in testDropTable (): \n"
                    + "Drop Table testDropTable threw an exception: " +(e.getMessage()));
        }
    }
    else{
        con.close();
    }
}

I think I need to change this two line:

String dropTable = "DROP TABLE ";
String[] tables = DB_TABLE;

can I change DROP TABLE to data_table and what about second line. What is this DB_TABLE I change the whole code by this way but till problem is there:

public void testDropTable () throws SQLException{
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }
    Connection con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");
    Statement stmt = con.createStatement();
    ResultSet checkTable = con.getMetaData().getTables(null, null, "POI", null);
    String tableName = null;
    while (checkTable.next())
    {
        System.out.println("In here");
        tableName = checkTable.getString("data_table");
        System.out.println(tableName);
    }
    if (tableName != null){
        try {
            String dropTable = "DROP TABLE ";
            String[] tables = {"data_table"};
            for (int i = 0; i < tables.length; i++){
                String stringCode = new String();
                stringCode = stringCode + tables[i];
                System.out.println(dropTable + tables[i]);

                // Drop each table in the array.
                int temp = stmt.executeUpdate(dropTable + tables[i]);
            }
        }
        catch (Exception e) {
            System.err.println("Exception in testDropTable (): \n"
                    + "Drop Table testDropTable threw an exception: " +(e.getMessage()));
        }
    }
    else{
        con.close();
    }
}

Answer 1

For anybody interested in this question, i removed the while loop statement in the accepted answer and reduced the code to:

public void testDropTable() throws SQLException, ClassNotFoundException {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");
        Statement stmt = con.createStatement();
        String[] tables = {"data_table"};
        for (String table : tables) {
            try {
                stmt.executeUpdate("DROP TABLE " + table);
            } catch (SQLException e) {
                System.err.println("Exception in testDropTable (): \n"
                    + "Drop Table testDropTable threw an exception: " +(e.getMessage()));
            }
        }
        con.close();
    }

Answer 2

try this code

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");
Statement stmt = con.createStatement();
// Specify the type of object; in this case we want tables
String[] types = {"TABLE"};
ResultSet checkTable = con.getMetaData().getTables(null, null, "%", types);
String tableName = null;

while (checkTable.next())
{
    System.out.println("In here");
    tableName = checkTable.getString(3)
    System.out.println(tableName);

    // check if the table 'data_table' exist in your database
    if (tableName.equals("data_table"){    
        try {
            //drop the table if present  
            int temp = stmt.executeUpdate("DROP TABLE " + tableName);
            break;
        }
        catch (Exception e) {
            System.err.println("Exception in testDropTable (): \n"
                + "Drop Table testDropTable threw an exception: " +(e.getMessage()));
        }
    }    
}
con.close; 

for more information visit here Metadata

Answer 3

Drop table table name; is the command to drop the table in your database. Try to replace the DB_TABLE with data_table.

String dropTable = "DROP TABLE ";
String[] tables = data_table;

---

try this

    public void testDropTable () throws SQLException{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");
    Statement stmt = con.createStatement();
    ResultSet checkTable = con.getMetaData().getTables(null, null, "POI", null);
    String tableName = null;
    while (checkTable.next())
    {
        System.out.println("In here");
        tableName = checkTable.getString("data_table");
        System.out.println(tableName);
    }
    if (tableName != null){
        try {
            String dropTable = "DROP TABLE ";
            String[] tables = tableName;
            for (int i = 0; i < tables.length; i++){
                String stringCode = new String();
                stringCode = stringCode + tables[i];
                System.out.println(dropTable + tables[i]);

                // Drop each table in the array.
                int temp = stmt.executeUpdate(dropTable + tables[i]);
            }
        }
        catch (Exception e) {
            System.err.println("Exception in testDropTable (): \n"
                    + "Drop Table testDropTable threw an exception: " +(e.getMessage()));
        }
    }
    else{
        con.close();
    }
}