SushantNayak's Techincal Weblog

Technology that matters…

  • October 2010
    M T W T F S S
    « Jan   Jan »
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Blog Stats

    • 57,477 hits
  • OOM Inc

Connect MS-Access using jdbc-odbc in Windows 7 64-bit

Posted by sushantnayak on October 25, 2010


Connect MS-Access using jdbc-odbc in Windows 7 64-bit

I faced this problem while connecting to MS-Access using JDBC-ODBC driver, on my laptop and found out this solution.

Bye-the-way I received the following error initially(in my output console).

Exception: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

My solution as follows…

Step 1

Run the 32-bit odbc driver using

WinKey+R, then copy-paste the below command

C:\Windows\SysWOW64\odbcad32.exe
Step 2

Make a dsn named “AccessDB” or whatever name you want to.

Step 3

Create a new project in eclipse.

Step 4

Change the jre to the java installed inside

C:\Program Files (x86)\java

Use this as “JRE System Library”

Step 5

Use the below code to connect to connect mdb file named “library.mdb”(choose ur mdb file), having the path as

“D:\Study\library.mdb”

import java.sql.Connection;
import java.sql.DatabaseMetaData;

public class Main {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection con = null;
	    try {

	// Setting up the DataSource object
	      sun.jdbc.odbc.ee.DataSource ds
	        = new sun.jdbc.odbc.ee.DataSource();
	      ds.setDatabaseName("AccessDB");
	      ds.setDataSourceName("D:\\Study\\library.mdb");
	// Getting a connection object
	      con = ds.getConnection();

	// Getting database info
	      DatabaseMetaData meta = con.getMetaData();
	      System.out.println("Server name: "
	        + meta.getDatabaseProductName());
	      System.out.println("Server version: "
	        + meta.getDatabaseProductVersion());

	// Closing the connection
	      con.close();
	    } catch (Exception e) {
	      System.err.println("Exception: "+e.getMessage());
	    }
	  }
}

Viola…
Output
Server name: ACCESS
Server version: 04.00.0000

(for any clarification, do ask in my blog, I’ll be happy to explain)…

peace out…

About these ads

56 Responses to “Connect MS-Access using jdbc-odbc in Windows 7 64-bit”

  1. karthikeyan ellappan said

    hai,,,,
    i am having windows 7 32 bit os…..in my os C:\Windows\SysWOW64\odbcad32.exe path is not available…..
    then how i connect access with Driver….please help me….thanks advance…..

  2. vaibhav said

    thanks very much for the solution!!!!
    worked effectively on my system.

    • sajid said

      yaar kaise 3 , 4 step pura kiya jara mail karo please bahut paresan hu is connection se
      3 step kaise kiya
      4 step kaise kiya

  3. Gobruen said

    Connecting to MS Access on Windows 64-Bit

    Folks, like many of you I struggled with the deprecation of JET 4.0 in Windows Vista/7 on 64-bit machines. A process running simply for years, ported to a faster machine simply stopped working with the error: ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered. I was annoyed at the lack of notice from MS and lack of clear instructions. After tinkering I found the solution and I’ll post it first directly, with details after, for the benefit of those just as frustrated as I. The situation I describe is for VB, but may work in other code/environments.

    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

    There is no need to upgrade/replace JET or emulate 32-bit connections. You don’t have to buy a new version of MS Office/Access, Access does not even need to be installed on the machine, you just need the driver. No need to convert to SQL Express. My legacy code is now running flawlessly on Windows 7 quad 64-bit machine without any office apps installed.

    When I first encountered the issue I tried to replace/upgrade JET but it was not available for 64-Bit and there were no plans to create it. Apparently, there is now a version released for 64-bit windows but you don’t need it. The MS Access Driver exists on older platforms as well and can be used instead of JET on 32-bit machines as well. However, there is one important detail concerning “*.accbd”, you must include this in the string or it wont work. Many examples posted on the web look like this: Driver={Microsoft Access Driver (*.mdb)} But it will produce an error in some cases indicating the DB and driver were not supplied in the string. *.accdb needs to be included as well.

    Replace: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

    With: Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=

    Full pseudo code:

    dbLocation = "C:\dbstore\myAccess.mdb"
    Set objADO = CreateObject("ADODB.Connection")
    objADO.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & dbLocation

    All other SQL calls and objects are unchanged.

    Have not tried yet in C++, or with Excel, or as a DNS location, but test it yourself.

    • Jan said

      Hi.

      I know that you didn’t test your method with Java (and consequently didn’t vouch for it working with Java), but it would appear that it should work and yet I got an SQLException for the connection when I attempted it. I was wondering whether you or anybody else reading this could shed any light on the problem. Shown below is the Java code that I ran after running AccessDatabaseEngine_x64.exe.

      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 with this problem would be much appreciated.

      Thanks.
      Jan

  4. Krishanu said

    Thank You very much…its working very good

  5. Neha said

    can u please tell me how to Change the jre to the java installed inside

    C:\Program Files (x86)\java

    using netbeans ?

  6. hi am using windows 7 64 bit,when i connect jdbc the administrative tools contains only sql driver not for ms access driver…how can i install that and how can i create dsn….pls help me….send the download link for ms access driver….please

  7. middleman said

    middleman…

    [...]Connect MS-Access using jdbc-odbc in Windows 7 64-bit « Sushantnayak’s Techincal Weblog[...]…

  8. Windows 8 Keyboard Shortcuts…

    [...]Connect MS-Access using jdbc-odbc in Windows 7 64-bit « Sushantnayak's Techincal Weblog[...]…

  9. import java.sql.*;

    public class JDBCTest2
    {
    public static void main(String[] args)
    {
    Connection connection = null;
    Statement statement = null;
    ResultSet results = null;
    String fileName = “D:\\Documents\\Projects\\Java\\SIP\\SIP.mdb”;
    String dbString = “jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=” + fileName + “;DriverID=22;READONLY=false}”;

    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! *”);
    System.exit(1);
    }

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

    try
    {
    while(results.next())
    {
    System.out.println(“Kode barang: ” + results.getInt(0));
    System.out.println(“Nama: ” + results.getString(1));
    }
    }
    catch (SQLException sqlEx)
    {
    System.out.println(“* Error retrieving data! *”);
    System.exit(1);
    }
    }
    }

    Thats my code and my requirement :
    1. Java installed in Program Files not in Program Files (x86)
    2. And the output “* Cannot execute query! *”

    Pliss help me…!!!

  10. Sorry the output is * Cannot connect to database! *

  11. JSupport said

    Connect to Access Database using Java

    http://javasrilankansupport.blogspot.com/2012/02/connect-to-access-database-using-java.html

  12. Software said

    Software…

    [...]Connect MS-Access using jdbc-odbc in Windows 7 64-bit « Sushantnayak's Techincal Weblog[...]…

  13. anil said

    iam unable to follow step 4

  14. cadeau Maude…

    [...]Connect MS-Access using jdbc-odbc in Windows 7 64-bit « Sushantnayak's Techincal Weblog[...]…

  15. rahul kumar gupta said

    sir,
    i want to connect with oracle 10g with windows 7 (type 1 jdbc -odbc driver)
    give me solution

  16. Pratik said

    Thanks yaar…..
    You solved my problem……….
    finally connected with odbc………

  17. Kondy said

    I m facing the same problem ,javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified,i have applied all the things but it won’t work kindly suggest if anyone have the solution

  18. lohitha said

    i have window 7 64-bit i cant access the jdbc connection in dreamweaver

  19. Amit singh said

    how to create eclipse and from where

  20. Prince said

    After following above steps i am getting this exception :
    unable to obtain connectionjava.sql.SQLException: [Microsoft][ODBC Driver Manage
    r] The specified DSN contains an architecture mismatch between the Driver and Ap
    plication

  21. yanamandra said

    I am getting below error in windows 7 64bit home basic edition while doing java-jdbc program connecting with oracle…..
    Fatal NI connect error 6413, connecting to:
    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleXE)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’)))(CONNECT_DATA=(SID=XE)(CID=(PROGRAM=C:\Program Files (x86)\Java\jdk1.7.0\bin\java.exe)(HOST=PRASAD-VAIO)(USER=Prasad))))

    VERSION INFORMATION:
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    Time: 14-SEP-2012 21:44:03
    Tracing not turned on.

  22. rohit kshirsagar said

    i want to connectivity of database to html web page……
    & also jdbc odbc drivers for windows7

  23. Sweet blog! I found it while browsing on Yahoo News. Do you have any tips on how to get listed in Yahoo News?

    I’ve been trying for a while but I never seem to get there! Thanks

  24. Mohsin said

    when i tried this technique i am having following bub

    sun.jdbc.odbc.ee.DataSource cannot be resolved to a type

    how to resolve this problem??

  25. Rajesh said

    what it mean Exception: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

  26. Harsha said

    Hi everyone,

    I am unable to connect to my database from JSP page. The below mentioned is the error that i am facing after recently changing my MS office from 2010 to 2007. Is their anything that can be done in order to rectify this problem

    HTTP Status 500 – javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

  27. i did one project on java servlet but database connectivity in windows xp 32-bit os can be done but it is not connecting in windows 7.
    and one thingis it is getting connected for some of the computers only. why we are facing this problem?

  28. anurag said

    How to make window application in eclipse ….
    means i have install eclipse into 64 bit machine ..want to design form base application,i.e. com box check box etc using drag and drop

  29. I’m not positive the place you are getting your info, however good topic. I must spend a while finding out more or figuring out more. Thanks for great info I used to be looking for this information for my mission.

  30. saicharan said

    Hi sushant
    I am using windows 7 64 bit ,i repeated all the steps that u explained…but i am still getting the output

    Exception: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    please resolve the issue.

    • Anurag said

      Do the following things to connect the access data base to java program in 64 bit machine.

      go to control panel—-> administrative tool—> Data Source (ODBC)(click right and set the proprieties )

      Target—C:\Windows\SysWOW64\odbcad32.exe and start in —->C:\Windows\SysWOW64
      then create the dsn name of your java project

  31. Outstanding post however , I was wanting to know if you could write a
    litte more on this subject? I’d be very thankful if you could elaborate a little bit further. Appreciate it!

  32. Contact said

    Wonderful blog! I found it while searching
    on Yahoo News. Do you have any tips on how to get listed in Yahoo News?

    I’ve been trying for a while but I never seem to get there! Cheers

  33. Paulette said

    Hi there very cool site!! Man .. Excellent .
    . Superb .. I will bookmark your site and take the feeds additionally?
    I’m happy to find a lot of useful info here within the submit, we need develop extra strategies in this regard, thank you for sharing. . . . . .

  34. saidbryan said

    Hi i use netbeans and i didn’t found jre system library so pls help me to connect access to java project

    • Anurag said

      Do the following things to connect the access data base to java program in 64 bit machine.

      go to control panel—-> administrative tool—> Data Source (ODBC)(click right and set the proprieties )

      Target—C:\Windows\SysWOW64\odbcad32.exe and start in —->C:\Windows\SysWOW64
      then create the dsn name of your java project

  35. This website was… how do you say it? Relevant!
    ! Finally I have found something that helped me.
    Thanks!

  36. badass said

    Hey would you mind sharing which blog platform you’re working with? I’m planning to start my own blog soon but I’m having a difficult time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I’m looking for something completely
    unique. P.S Sorry for getting off-topic but I had to ask!

  37. My partner and I stumbled over here from a different web address and thought I might check
    things out. I like what I see so i am just following you.
    Look forward to looking over your web page repeatedly.

  38. Everyone loves what you guys tend to be up too. Such clever work
    and exposure! Keep up the amazing works guys
    I’ve included you guys to our blogroll.

  39. Howdy! Do you know if they make any plugins to assist with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results.
    If you know of any please share. Kudos!

  40. It’s amazing to go to see this web page and reading the views of all mates concerning this piece of writing, while I am also zealous of getting know-how.

  41. It’s difficult to find knowledgeable people on this subject, however, you seem like you know what you’re talking about!
    Thanks

  42. Veeru.Bond said

    Thanks a ton….it worked beautifully :0

  43. This human touch cannot be duplicated by the software programs currently available.
    Many home sourcing employers not only prefer but require that employees live in the area.
    I have spent years trying to find a way to work from home and I finally succeeded.

  44. online medical assistant said

    Good post. I learn something new and challenging on sites I stumbleupon
    every day. It will always be useful to read through articles from other authors and practice something from their sites.

  45. Nark Jums said

    Hey, I have noticed that on occasion this website shows an 404 server error. I figured you would like to know. Best wishes

  46. We appreciate you the actual excellent writeup. It was a amusement bank account that. Appear superior to be able to considerably extra gratifying from you finding out! Incidentally, exactly how should we communicate?

  47. Dhaval Patel said

    thaxx for gret solution…thats a connectivity between servlet/jsp to odbc in 64 bit…..

  48. Howdy! This post couldn’t be written any better!

    Reading through this postt reminds me of my previous room mate!
    He always kept chatting about this. I will forward this post
    to him. Fairly certain he will have a good read.
    Thanks for sharing!

  49. Je prends la peine d’écrire un commentaire uniquement pour congratuler l’auteur

  50. Bon article : continuez comme ça

  51. cadeau said

    What’s up, all is going nicely here and ofcourse every one is sharing facts, that’s
    genuinely excellent, keep up writing.

    – cadeau
    – cadeau
    – cadeau
    – cadeau
    – cadeau
    – cadeau

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 3,846 other followers

%d bloggers like this: