This discussion is archived
9 Replies Latest reply: Jun 28, 2011 9:29 AM by 869062 RSS

Connecting to MS Access In Windows 7 64-Bit

869062 Newbie
Currently Being Moderated
Hi.

As usual, Microsoft has done its best to make this as difficult as possible. The essence of a solution to this problem that I found at http://sushantnayak.wordpress.com/2010/10/25/connect-ms-access-using-jdbc-odbc-in-windows-7-64-bit/ is as follows:

1. Download and run AccessDatabaseEngine_x64.exe
(http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en)

2. Change the connection string in your code to:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= <-db name and path here

Though the author of the tip had used VB to make the connection, it looked as though the same technique should work with Java. However, I found that the attempted connection kept generating an SQLException. The code that I tried to run is shown below.

import java.sql.*;

public class JDBCTest2
{
     public static void main(String[] args)
     {
          Connection connection = null;
          Statement statement = null;
          ResultSet results = null;
          String fileName = "F:\\Temp\\Finances.mdb";
/*
I also tried the following (with different fields below):
          String fileName = "F:\\Temp\\ExamResults.accdb";
*/
          String dbString =
               "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="
               + fileName + ";DriverID=22;READONLY=false}";
//I also tried the above without ';DriverID=22;READONLY=false', but the
//result was exactly the same.

          try
          {
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               connection = DriverManager.getConnection(dbString, "", "");
          }
          catch (ClassNotFoundException cnfEx)
          {
               System.out.println("* Unable to load driver! *");
               System.exit(1);
          }
          catch (SQLException sqlEx)
          {
               System.out.println("* Cannot connect to database! *");
               //The above message was displayed.
               System.exit(1);
          }

          try
          {
               statement = connection.createStatement();
               results = statement.executeQuery("SELECT * FROM Accounts");
          }
          catch (SQLException sqlEx)
          {
               System.out.println("* Cannot execute query! *");
               System.exit(1);
          }

          try
          {
               while(results.next())
               {
                    System.out.println("Account no. " + results.getInt(1));
                    System.out.println("Account holder: " + results.getString(3)
                                   + " " + results.getString(2));
                    System.out.printf("Balance: %.2f %n%n",results.getFloat(4));
               }

          }
          catch (SQLException sqlEx)
          {
               System.out.println("* Error retrieving data! *");
               System.exit(1);
          }
     }
}

Any help in solving this problem would be most appreciated.

Thanks.
Jan ('Yan')
  • 1. Re: Connecting to MS Access In Windows 7 64-Bit
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Use code tags when you post code.

    And you didn't post the Exception (nor stack trace.)
  • 2. Re: Connecting to MS Access In Windows 7 64-Bit
    857327 Newbie
    Currently Being Moderated
    I rarely connected ms access from java. but i connect .net from ms access and it is very simple out there. but i found something so there are two techniques available for connecting to a data source using JDBC. First, if your program will talk to an ODBC data source, you can use the JDBC-ODBC bridge included with your JDK. In the case of Sun, you would use

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver <program name>

    good luck!!

    Edited by: laylaa on Aug 21, 2012 8:30 AM
  • 3. Re: Connecting to MS Access In Windows 7 64-Bit
    EJP Guru
    Currently Being Moderated
    Laylaa, that is exactly* what he is doing.

    Please read the question before you post.
  • 4. Re: Connecting to MS Access In Windows 7 64-Bit
    869062 Newbie
    Currently Being Moderated
    Thanks, but that is exactly what I have used in the test program that I posted.
  • 5. Re: Connecting to MS Access In Windows 7 64-Bit
    869062 Newbie
    Currently Being Moderated
    I believe that my problem may be due to using a 32-bit Access database, rather than a 64-bit one. I am fully aware of how to use c:\windows\syswow64\odbcad32.exe to get round the problem of setting up the required ODBC data source, and that is what I shall be making use of for at least the next year, as I have just been informed that it will be the 32-bit version of MS Access (with Windows 7) that will be used at my institution for the coming academic session. (I lecture at a university.)

    As a consequence of the above, it turns out that I don't really have a problem for the foreseeable future. However, I would like to know (for personal satisfaction, if nothing else) whether the test program would work if it were used with a 64-bit Access database. Unfortunately, it looks as though I won't have the use of a 64-bit version of Access. Consequently, I would be most grateful if anybody out there could try out my test program with 64-bit Access. Obviously, the appropriate database would need to be set up (or an existing one used and the name/path changed accordingly), but the fields wouldn't even have to be set up to match mine. As long as the program gets past the 'Cannot connect to database error message', I'll be happy.

    Thanks.
    Jan
  • 6. Re: Connecting to MS Access In Windows 7 64-Bit
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    user4069340 wrote:
    As long as the program gets past the 'Cannot connect to database error message', I'll be happy.
    Either you ran your code or you didn't.

    If you did there was an exception.
    Print the stack trace of that exception. And post it.
  • 7. Re: Connecting to MS Access In Windows 7 64-Bit
    gimbal2 Guru
    Currently Being Moderated
    jschell wrote:
    user4069340 wrote:
    As long as the program gets past the 'Cannot connect to database error message', I'll be happy.
    Either you ran your code or you didn't.

    If you did there was an exception.
    Print the stack trace of that exception. And post it.
    catch (SQLException sqlEx)
    {
    System.out.println("* Cannot connect to database! *");
    //The above message was displayed.
    System.exit(1);
    }
    There is no exception stacktrace because the code swallows it.
  • 8. Re: Connecting to MS Access In Windows 7 64-Bit
    802889 Explorer
    Currently Being Moderated
    user4069340 wrote:
    I believe that my problem may be due to using a 32-bit Access database, rather than a 64-bit one. I am fully aware of how to use c:\windows\syswow64\odbcad32.exe to get round the problem of setting up the required ODBC data source, and that is what I shall be making use of for at least the next year, as I have just been informed that it will be the 32-bit version of MS Access (with Windows 7) that will be used at my institution for the coming academic session. (I lecture at a university.)
    If you are using c:\windows\syswow64\odbcad32.exe and try to access the datasource from a 64 bit program that is exactly your problem: on 64 bit windows, all programs in syswow64 are the 32 bit programs of windows, and the odbcad32.exe in syswow64 will make datasources for 32 bit programs. The ODBC administrator you need for 64 bit programs is c:\windows\system32\odbcad32.exe (no kidding; whoever thought up this naming structure at Microsoft ought to be shot, repeatedly).

    Edited by: TheAvalanche on Jun 28, 2011 6:07 AM
  • 9. Re: Connecting to MS Access In Windows 7 64-Bit
    869062 Newbie
    Currently Being Moderated
    If you are using c:\windows\syswow64\odbcad32.exe and try to access the datasource from a 64 bit program that is exactly your >problem: on 64 bit windows, all programs in syswow64 are the 32 bit programs of windows, and the odbcad32.exe in syswow64 >will make datasources for 32 bit programs. The ODBC administrator you need for 64 bit programs is
    c:\windows\system32\odbcad32.exe (no kidding; whoever thought up this naming structure at Microsoft ought to be shot, repeatedly).
    Thanks for this, but I've already tried it. It leads to a dead-end, with no way of specifying the data source (after forcing a selection of SQL Server from a list containing only this one option).

    I suppose that I could be missing something, but I really don't see how.

    Thanks again, though.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points