CODEWITHSUNDEEP
javams-access
Dhinakar
Dhinakar

Reputation: 4151

How to create a table in access database using java

i need to create a table in access database. For this i tried with the following code

    public class Testac {

    public static void main(String[] args) {
        try {

            System.out.println("Begining conn");
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String accessFileName = "Centre";
            String connURL = "jdbc:odbc:;DRIVER=Microsoft Access Driver (*.accdb);DBQ=" + accessFileName + ".accdb;PWD=";
            Connection con = DriverManager.getConnection(connURL, "", "");
            Statement stmt = con.createStatement();
            System.out.println("Conn done succesfully");
            stmt.execute("create table student ( Name string, ID integer )"); // create a student
            stmt.execute("insert into student values(‘Md. SHAHJALAL’, ‘02223540’)"); // insert data into student
            stmt.execute("select * from student"); // execute query in table student
            ResultSet rs = stmt.getResultSet(); // get any Resultt that came from our query
            if (rs != null) {
                while (rs.next()) {
                    System.out.println("Name: " + rs.getString("Name") + " ID: " + rs.getString("ID"));
                }
            }
            stmt.close();
            con.close();
        } catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
    }
}

But it throws the following error " ERROR: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ".

Upvotes: 1

Views: 4302

Answers (3)

Prasath Bala
Prasath Bala

Reputation: 728

It is possible with UCanAccess

   con = ConnectMdb(homedirectory+"/"+"Centre.accdb");
    if (con != null) {
        Statement st3 = null;
        try {
            st3 = (Statement) con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataEntryScreen.class.getName()).log(Level.SEVERE, null, ex);
        }
        String sqlq3 = "CREATE TABLE REGISTRATION " +
               "(id INTEGER not NULL, " +
               " first VARCHAR(255), " + 
               " last VARCHAR(255), " + 
               " age INTEGER, " + 
               " PRIMARY KEY ( id ))";
        //   System.out.println(sqlq1);
     //   ResultSet rs3 = null;
        try {
             st3.execute(sqlq3);
        } catch (SQLException ex) {
            Logger.getLogger(DataEntryScreen.class.getName()).log(Level.SEVERE, null, ex);
        }

Try this.

Upvotes: 2

Tash Pemhiwa
Tash Pemhiwa

Reputation: 7685

The problem is on this line:

String connURL = "jdbc:odbc:;DRIVER=Microsoft Access Driver (*.accdb);DBQ=" + accessFileName + ".accdb;PWD=";

which you should change to:

String connURL = "jdbc:odbc:DRIVER=Microsoft Access Driver (*.accdb);DBQ=" + accessFileName + ".accdb;PWD=";

i.e. you need to remove the semi-colon between jdbc and odbc.

Upvotes: 0

Vishvesh Phadnis
Vishvesh Phadnis

Reputation: 2598

Below Corrected Code may help you:

mistake in connection string and while creating table. need to use executeUpdate method

public class Testac {

public static void main(String[] args) {
    try {

        System.out.println("Begining conn");
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String accessFileName = "Centre";
        String connURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + accessFileName + ".accdb;PWD=";
        Connection con = DriverManager.getConnection(connURL, "", "");
        Statement stmt = con.createStatement();
        System.out.println("Conn done succesfully");
        stmt.executeUpdate("create table student ( Name string, ID integer )"); // create a student
        stmt.execute("insert into student values(‘Md. SHAHJALAL’, ‘02223540’)"); // insert data into student
        stmt.execute("select * from student"); // execute query in table student
        ResultSet rs = stmt.getResultSet(); // get any Resultt that came from our query
        if (rs != null) {
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("Name") + " ID: " + rs.getString("ID"));
            }
        }
        stmt.close();
        con.close();
    } catch (Exception err) {
        System.out.println("ERROR: " + err);
    }
}
}

Upvotes: 0

Related Questions