java.sql.SQLException: Invalid handle

Question

I'm trying to teach myself how to connect to a msaccess database in java. I have set up a class to access the database as follows

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public abstract class AccessDBConnect2 {
    public static Connection connect(){
        String fileName = "C:/Users/Bridget/Documents/EmployeeSys.accdb";
        Connection con = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
            con = DriverManager.getConnection(url,"","");
        } catch (Exception e) {
            // Handle exceptions    ...
            System.out.println(e.toString());
            System.out.println("A problem accessing the database");
            e.printStackTrace();
        } finally {
            try { if(con!=null) {con.close();} } catch (Exception e) {}
        }
        return con;
    }
public static void closeConnection(Connection conn){
    try{
        conn.close();
    }catch (Exception e){

    }
}

Then I have my code which is just trying to select everything from the table. I have created the table in msAccess and the code seems to get through the connect method in the above code without any problems, indicating it is finding the database and accessing it somewhat. The problem happens when I call the prepareStatement using the connection, i.e. code line:

stm = conn.prepareStatement(sql);

The full code is:

import java.sql.*;
public class Program2{
public static void main(String[] args) {
        try{
            // Load the JDBC driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

        // Establishing db connection
        Connection conn = AccessDBConnect.connect();

        // Displaying all records from employee file
        System.out.println("Display records of all employees");
        display(conn);

        // Closing the connection
        AccessDBConnect.closeConnection(conn);
    }catch (Exception e){
        System.out.println("Error");
    }
}

// Display details of all employees
public static void display(Connection conn){
    PreparedStatement stm = null;
    // SQL statement
    String sql = "SELECT * FROM Employee";
    ResultSet rs;
    try {
        stm = conn.prepareStatement(sql);   // Prepare the SQL statement
        rs = stm.executeQuery();            // Execture the SQL statement

        // Navigate through the ResultSet and print
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String gender = rs.getString("gender");
            String address = rs.getString("address");

            System.out.println("ID: \t \t" + id);
            System.out.println("Name: \t \t" + name);
            System.out.println("Gender: \t" + gender);
            System.out.println("Address: \t" + address);
            System.out.println(" ");
        }

    // Closing the resultSet
    rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void test(){
    int a = "hello";
}

}

Answer 1

You are receiving the error because when you try to call .prepareStatement the connection is closed. Your AccessDBConnect2 class contains a finally block that closes the connection before it returns. Fix that class so it leaves the connection open.

By the way, the JDBC-ODBC Bridge has been removed from Java 8 and is effectively obsolete. You might be interested in this alternative:

Manipulating an Access database from Java without ODBC

Answer 2

I've removed the obviously incorrect answer :) another possibility:

I would think the issue is in your connection to the database, try changing 'C:/Users/Bridget/Documents/EmployeeSys.accdb'
to
'C:\\Users\Bridget\Documents\EmployeeSys.accdb'