JDBC driver MS Access connection

Question

I want connect my MS access file with Java GUI program,but I have problem with connection....

I have Windows 7 64b, and ms office 2007. When I opened the ODBC driver manager in the control panel I havent found any driver for Microsoft Access (maybe when I started the ODBC is started running the 64bit ODBC, now I think is running the 32bit ODBC. I read this and I make it : "jdbc-odbc connection for window 7 64 bit machine.. 1 . Right click Data source (ODBC)..go to properties change the folloing thing

target [ %SystemRoot%\SysWOW64\odbcad32.exe ] start in : [ %SystemRoot%\System32 ]

press enter and continue as admin source: source link " ) Now when I start in conctrol pannel the ODBC I can see the driver screenshoot

My program code(I tried two ways but I have same error):

        public void Connect() {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

 //    String DatabaseFile = "D:java/Invertory.mdb";       
//            String DATABASE =
//                    "jdbc:odbc:Driver="
//                    + "{Microsoft Access Driver (*.mdb, *.accdb)};"
//                    + "DBQ=" + DatabaseFile;`enter code here`
 String DATABASE ="jdbc:odbc:Driver= Microsoft Access Driver (*.mdb, *.accdb);DBQ=Invertory.mdb";
           CONEX = DriverManager.getConnection(DATABASE);

        } catch (Exception X) {
          X.printStackTrace();
            //JOptionPane.showMessageDialog(null,e);
        }
    }

error

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Answer 1

Use UCanAccess JDBC Driver :

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>",user, password); 
for example: 
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb");

So for your example it will be Connection conn=DriverManager.getConnection("jdbc:ucanaccess://"+path)

Answer 2

If you are using Windows 64-bit you probably need to go to this path

C:/Windows/SysWOW64/odbcad32.exe

Then I noticed that you are using the direct path instead creating new System DSN, your direct path is correct till the path to the access file you must give the full path like this :

jdbc:odbc:Driver= Microsoft Access Driver (*.mdb, *.accdb);DBQ=path/to/Invertory.mdb"

To get the path you probably need to use java.io.File that have a method returns the abslute path to the file see the example :

import java.sql.*;
public class TestConnection {
    Connection con ;
    Statement st ;
    ResultSet rs ;
    String db;
    public TestConnection (){
        try{
            String path = new java.io.File("Invertory.mdb").getAbsolutePath();
        db ="JDBC:ODBC:Driver=Microsoft Access Driver (*.mdb, *.accdb); DBQ="+path;
            doConnection();
        } catch(NullPointerException ex){
                ex.printStackTrace();
            }

    }

    public void doConnection(){
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection(db);
            st = con.createStatement();
            rs = st.executeQuery("select * from Invertory");
            while(rs.next()){
                System.out.println(rs.getObject(1));
            }
        }catch(SQLException | ClassNotFoundException ex){
            System.out.println(ex.toString());

        }

    }
    public static void main(String...argS){
        new TestConnection();
    }
}

Answer 3

I answered a similar question enter link description here a while back.

Basically at that time:

1. You could connect to Ms-Access from 32 bit java through the JDBC-ODBC bridge
2. You could not connect to a 32 bit Odbc driver through the JDBC-ODBC from 64 bit java. There was a message telling you that you can only connect from a 32 bit programs
3. While Microsoft does provide a 64 bit Ms-Access driver, it did not work with Java's 64 bit JDBC-ODBC driver.

Since then there seems to be a new open-source Ms-Access JDBC Driver Ms-Access JDBC driver. I have no Idea how good it is.