Connecting a Microsoft Access Database to Java using JDBC and compiling

Question

for a school database project we are making a database program (user GUI and the database). Using Microsoft Access 2010 I created the database and populated it with some sample data, and saved it in .mdb format and placed it in my project folder.

When running it in eclipse the following code works fine, connects and even retrieves the query. However I find that I am unable to export the code to a jar and run it (which is required for the project, give them a working copy of your program on a CD or flash drive), and I'm also unable to port the code over to Netbeans to have it work, as well as trying to compile on a Linux machine.

I assume this is a problem with including drivers or trying to use Microsoft access. The error I get when running the jar or running on Netbeans is given below the code. So I ask either how do I include drivers to make the program portable, or how else can I approach this problem?

Thanks in advance

import java.sql.*;

public class JDBCTest {
    static Connection connection;
    static Statement statement;

    public static void main(String args[]){

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=TLDATABASEDBM.mdb";
            connection = DriverManager.getConnection( database ,"",""); 

            buildStatement();
            executeQuery();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("Error!");
        }
    }

    public static void buildStatement() throws SQLException {
        statement = connection.createStatement();
    }

    public static void executeQuery() throws SQLException {

        boolean foundResults = statement.execute("SELECT * FROM tblStaff  AS x WHERE City='Calgary'");
        if(foundResults){
            ResultSet set = statement.getResultSet();
            if(set!=null) displayResults(set);
        }else {
            connection.close();
        }
    }

    public static void displayResults(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columns=metaData.getColumnCount();
        String text="";

        while(rs.next()){
            for(int i=1;i<=columns;++i) {
                text+=""+metaData.getColumnName(i)+":\t";
                text+=rs.getString(i);
                //text+="</"+metaData.getColumnName(i)+">";
                text+="\n";
            }
            text+="\n";
        }

        System.out.println(text);

    }
}

The error mentioned above:

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:6957)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
        at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
        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:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at tldatabase.DataConnect.makeConnection(DataConnect.java:35)
        at tldatabase.Main.main(Main.java:24)

Solution 1

I know the post was years ago but I felt like answering the question for those who are just experiencing this right now. It took me a while to know the answer to the question so here's the solution:

http://wiki.netbeans.org/FaqSettingHeapSize

Follow the "Running the 32-bit JVM".

All you have to do is find the netbeans.conf in the installation folder of your netbeans and change the directory from something like this:

netbeans_jdkhome="C:\Program Files\Java\jdk1.6.0_24"

to this:

netbeans_jdkhome="C:\Program Files (x86)\Java\jdk1.6.0_21"

The problem is netbeans might be running in 64 bit but MS Access only support 32-bit. So doing this would hopefully solve the problem. Also make sure to install this:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734

Solution 2

The main problem lies in the line:

String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=TLDATABASEDBM.mdb";

1. Make sure that the .mdb file is in the correct directory.
2. Check the file extension as .mdb or .mdbacc.

Also, if you want to use the same DSN every time, it is better to add the DSN(Data Source Name) into the respective system on which the mdb is stored.

Comments

[Moogle]

Well testing with the working eclipse version of the code, if the path to the file is incorrect I get a cannot open unknown file error, not a "Data source name not found and no default driver specified" error. Im not sure if I can use a DSN and have the jar work because I need to package the entire database program and give it to the teacher to run. I attempted to use the DSN route before but I had some trouble getting it working, I will try again and ask if I can make a read me to run the program when i give it to the TA for the demonstration. Thanks for the help though.

[Moogle]

Alright so it works with a DSN as a jar file. My only question now is this wont work if they run the program on a non windows computer, as only windows has the drivers to connect to ms access files, right? and I assume there is no other way to connect to a ms access .mdb file using java? Thanks!

[Michał Niklas]

I tested it using Jython with Northwind Access database. It worked with connection string containing full .mdb file name and with DSN. I'm not sure if in Java you have to add newInstance() after Class.forName(). In Java6 you even not need to call Class.forName(). Have a look at onjava.com/pub/a/onjava/2006/08/02/….

[Anuja]

That is the actual name of your driver in Data Sources so you resolved it.