9

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)
Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
Moogle
  • 113
  • 1
  • 1
  • 5

5 Answers5

10

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

Darwin
  • 189
  • 2
  • 9
1

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.

Nick Dickinson-Wilde
  • 1,015
  • 2
  • 15
  • 21
Anil_irocks88
  • 109
  • 1
  • 3
0

Honestly, I dont like what I am going to say... but, it solved the same issue for me... mysteriously... :(((

on the line where you are defining the database variable, I changed ...(.mdb)... into ...(.mdb, *.accdb)...

All the best for figuring out what difference that made!

Vasu
  • 1
0
package javaapplication1;

import java.sql.*;

public class MSaccess_archive {
public static void main(String[] args) {

    try {

       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// set this to a MS Access DB you have on your machine
String filename = "mdbTEST.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};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 ,"","");

        Statement stmt = con.createStatement();

        stmt.execute("select * from student"); // execute query in table student

        ResultSet rs = stmt.getResultSet(); // get any Result that came from our query

        if (rs != null)
         while ( rs.next() ){

            System.out.println("Name: " + rs.getInt("Age") + " ID:       "+rs.getString("Course"));
            }

            stmt.close();
            con.close();
        }
        catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
   }

}
Joseph Dominguez
  • 1
0

I think that your app do not see TLDATABASEDBM.mdb in current directory. You can give full path to this file in connection string or add system DSN in ODBC Manager and then connect to it with connection string like: jdbc:odbc:TLDATABASEDBM

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • 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 Dec 01 '10 at 18:07
  • 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! – Moogle Dec 01 '10 at 18:21
  • 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 http://onjava.com/pub/a/onjava/2006/08/02/jjdbc-4-enhancements-in-java-se-6.html. – Michał Niklas Dec 01 '10 at 21:06