Insert data to ms access from java

Question

I'm new to JDBC. I want to insert data into Access from Java, but I can't get it. It shows the following error:

Connection Established Successfully  
java.sql.SQLException: General error  
Could Not Connect to Database  
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)  
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)  
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)  
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)  
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)  
    at DBConnect.<init>(DBConnect.java:22)  
    at DBConnect.main(DBConnect.java:32)  

code:

public DBConnect() {
    File f = new File("DB.accdb");
    try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        System.out.println("DriverLoaded");
        String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + f.getAbsolutePath();
        Connection con = DriverManager.getConnection(url);
        System.out.println("Connection Established Successfully");

        Statement st=con.createStatement();
        String productId="1";
        String desc="Jambu";
        int quantity=10;
        double price = 2.0, disc=1.0;
        st.executeUpdate("INSERT into Product(productID,description,quantity,price,discount) VALUES('"+productId+"','"+desc+"','"+quantity+"','"+price+"','"+disc+"')");
        System.out.println("Row is added");
    }catch(Exception e) {
        e.printStackTrace();
        System.out.println("Could Not Connect to Database");
    }

Solution

you have not installed driver for MSAccess properly..

For example try like this..

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  // set this to a MS Access DB you have on your machine
   String filename = "d:/DB.accdb";
   String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=";
   database+= filename.trim() + ";DriverID=22;}"; // add on to the end 
   // now we can get the connection from the DriverManager
   Connection con = DriverManager.getConnection( database ,"",""); 

And also make sure that you have import jar file of ODBC driver in your path..

Update :

Insert data like this..

PreparedStatement pstmt = (PreparedStatement) con.prepareStatement("insert into product(productID,description,quantity,price,discount) values(?,?,?,?,?)");
           pstmt.setString(1, productId);
           pstmt.setString(1, desc);
           //same for all statement
           pstmt.executeUpdate();
           pstmt.close();

Comments

[Gord Thompson]

{Microsoft Access Driver (*.mdb, )} is incorrect. It must be either {Microsoft Access Driver (*.mdb)} or {Microsoft Access Driver (*.mdb, *.accdb)}

[Java Man]

@GordThompson..sorry forget to write..,

[Gord Thompson]

{Microsoft Access Driver (*.mdb,*.accdb )} is incorrect, too. The space comes before .accdb, not after. Also, do you think that READONLY=true is a good idea when the object of the exercise is to do an INSERT on the database?

[Java Man]

@GordThompson.. no ofcourse not good idea but I am trying to solve connection error not have concern about inserting or selecting?

[Hazim Ali]

still did not resolve the problem.. it state error on the line connection con.. java.sql.SQLException: General error at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at DBcon.<init>(DBcon.java:24) at DBcon.main(DBcon.java:34) Could Not Connect to Database

[gumuruh]

do you need to include the java JAR library for operating in Ms.Access ? @JavaMan