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");
    }

Java Man · Accepted Answer · 2013-11-20T10:30:22.440

4

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();

edited Nov 20 '13 at 10:30

answered Nov 20 '13 at 09:05

Java Man

1,854
3
21
43

`{Microsoft Access Driver (*.mdb, )}` is incorrect. It must be either `{Microsoft Access Driver (*.mdb)}` or `{Microsoft Access Driver (*.mdb, *.accdb)}` – Gord Thompson Nov 20 '13 at 09:15
@GordThompson..sorry forget to write.., – Java Man Nov 20 '13 at 09:17
`{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? – Gord Thompson Nov 20 '13 at 09:23
@GordThompson.. no ofcourse not good idea but I am trying to solve connection error not have concern about inserting or selecting? – Java Man Nov 20 '13 at 09:28
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.(DBcon.java:24) at DBcon.main(DBcon.java:34) Could Not Connect to Database – Hazim Ali Nov 20 '13 at 10:17
do you need to include the java JAR library for operating in Ms.Access ? @JavaMan – gumuruh Oct 31 '21 at 10:49

Insert data to ms access from java

1 Answers1

Linked