Java connect to Access Database

Question

Im trying to be able to access data from an access database.

I have configured the odbc driver for *.mdb and *.accdb files under the ODBC Administrator. I am using windows 7 through parallels(not sure if that makes a difference)

When i run the program I get the following error:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no     default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3080)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at DataAccess.main(DataAccess.java:13)

start code:

import java.sql.*;

public class DataAccess 
{
public static void main(String[] args)
{
    try
    {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String connURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+"C:\\Users\\alexmac\\Desktop\\OASDatabase\\OAS_Database";

            String table = "Student";        
            Connection conn = DriverManager.getConnection(connURL, "", "");
        Statement s = conn.createStatement();

              // Fetch table
        String selTable = "SELECT * FROM " + table;
        s.execute(selTable);
        ResultSet rs = s.getResultSet();
        while((rs!=null) && (rs.next()))
        {
            System.out.println(rs.getString(1) + " : " + rs.getString(2));
        }

        // close and cleanup
        s.close();
        conn.close();
    }
    catch(Exception ex)
    {
        ex.printStackTrace();
    }
}
}

You might also look into the jacksess library, which also works on all platforms (i.e. not windows-specific). In my opinion, it's also easier to use than the JDBC/ODBC oriented driver. — Steinar
No, Driver={MSAccessDriver ...} won't work either. Take another look at my answer. — Gord Thompson
changed the driver to match your answer i believe. still getting the same error. I downloaded the 32 bit and 64 bit jdk to have both. — user214577
What does System.out.println(System.getProperty("sun.arch.data.model")); say? — Gord Thompson

Gord Thompson Gord Thompson · Accepted Answer · 2013-11-13T19:50:29

In addition to the double-extension problem (.accdb.accdb) mentioned in the comment above, your code won't work because the driver name you specified...

DRIVER={Microsoft Access Driver (*.accdb)}

...is incorrect. There is no ODBC driver with that name. 32-bit applications that want to open an older .mdb database file can use

Driver={Microsoft Access Driver (*.mdb)}

To open an .mdb file from a 64-bit application, or to open an .accdb file from any application, you need to use

Driver={Microsoft Access Driver (*.mdb, *.accdb)}

Java connect to Access Database

1 Answers