MS ACCESS jdbc.odbc connection. data source name not found/No default driver specified?

Question

I'm trying to study for a basic SQL test at school but unfortunately I copied the class that we are supposed to use into a project on my pc and I am getting the following error:

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

package Question1;

// Your name, Q 1
import java.sql.*;
import java.io.*;
import javax.swing.*;

public class GreenWood
{
 // Set up database connection
   private static final String DATABASE_FILE_NAME = "WoodDB.mdb";
   private static final String DRIVER = "jdbc:odbc:DRIVER=" +
   "{Microsoft Access Driver (*.mdb)};" +
   "DBQ=" + new File (DATABASE_FILE_NAME).getAbsolutePath ();
  static
  {
     try
     {
        Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
     }
         catch (ClassNotFoundException e)
        {
           System.out.println ("Class not found");
           e.printStackTrace ();
        }
  }


  private Connection dbcon;
  private BufferedReader keyb = new BufferedReader (new InputStreamReader (System.in));

   public GreenWood ()
  {
     System.out.println ("WoodDB Connection");
     try
     {
        dbcon = DriverManager.getConnection (DRIVER);
        Statement stmt = dbcon.createStatement ();
        System.out.println ("Connection successful\n");

        char choice = ' ';
        do
        {

         //Prints options for user input

           choice = keyb.readLine ().toUpperCase ().charAt (0);
           System.out.println (" ");
           switch (choice)
           {
             //calls query methods based on user input

           }
        }
        while (choice != 'X');
        dbcon.close ();
        System.out.println ("Done");
        Thread.sleep (1000);
        System.exit (0);
     } // try
         catch (Exception e)
        {
        // process exceptions here
           System.out.println ("Connection unsuccessful");
           e.printStackTrace ();
           System.out.println (e.toString ());
        }
  } // HoutSoorte constructor

  //Query Methods
  //Main creates new instance of GreenWood

my WoodDB database is located in the root project directory.

I Have done some troubleshooting and I believe that the problem is the URL of the driver location;

dbcon = DriverManager.getConnection (DRIVER);

DRIVER being:

private static final String DRIVER = "jdbc:odbc:DRIVER=" +
   "{Microsoft Access Driver (*.mdb)};" +
   "DBQ=" + new File (DATABASE_FILE_NAME).getAbsolutePath ();

After about an hour of research I'm still just as confused as I was. If anyone can help this nub programmer(me) by explaining the problem in baby words and how I can fix it, it would be ever appreciated.

Solution 1

Try using 32-bit JVM. I am getting the same error message when trying to connect from 64-bit JVM.

Solution 2

Try the following if it works:

For 64 bit system, Goto: C:\windows\sysWOW64. For 32 bit system, Goto: C:\windows

There is an executable called odbcad32.exe.

Run this exe as administrator to gain access to all the ODBC drivers that come with Microsoft Office, etc.

Create data source named my_data_source and mention the connection string as:

Connection con = DriverManager.getConnection("jdbc:odbc:my_data_source");

Above solution worked in my case.

Please refer: Java Connectivity with MS Access for details.

Comments

[Ross Borchers]

Isnt the same thing achieved b the use of file.getAbsolutePath(); ?

[user1335794]

probably not, File object should able to find a file first, then getAbsolutePath() could work.

[Ross Borchers]

It Works! thanks! as I said to Andrew earlier odbc is included by default in the JDK, but there is only a 32bit version. changing the jvm is not a great(or permanent) solution, but supposedly ODBC is outdated and should not be used unless it is completely necessary.(like in the case of an unfortunate school student)

[Pedro]

I recently asked how to solve this issue, but haven't had time yet to try the suggested solution. Anyway, in case you are interested have a look: stackoverflow.com/questions/10289655/….