creating jdbc odbc connection without creating the DSN in MS ACCESS

Question

I am trying to make the connection between java and ms access database. I want to make the connection without creating the DSN. I am using the following code but it is throwing the exception "Data source name not found exception "

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

     Connection con=DriverManager.getConnection("Jdbc:Odbc:Driver={Microsoft Access
     Driver(*.mdb)}; dbq=d:/newfolder/db11.mdb");
     Statement st=con.createStatement();
     }
     catch(Exception ex)
    {
     ex.printStackTrace();
     }

[already answered here, this may help you ][1] [1]: http://stackoverflow.com/a/5016979/1655086 — Raju, Jan 04 '13 at 13:04

score 1 · Accepted Answer · answered Jan 04 '13 at 13:12

1

it should be like this:

Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=/db.accdb");

answered Jan 04 '13 at 13:12

AsirC

449
1
6
20

Dear Alvin using the same command but its giving the Exception as Data source name not found exception. – Adesh singh Jan 05 '13 at 09:51
Is the database file located at your project path? – AsirC Jan 05 '13 at 10:06
i can allocate it but now its located somewhere else – Adesh singh Jan 05 '13 at 10:32
Dear the problem start from Driver= because my program considers it as dsn name – Adesh singh Jan 05 '13 at 10:33

score 1 · Answer 2 · answered Jul 05 '13 at 02:35

I also had this problem and tried many of the suggestions here and on various forums. Finally, I discovered a snippet from one place which led to success connecting and also explains why many of these posts do not work. See http://www.coderanch.com/t/295299/JDBC/databases/jdbc-odbc-DSN-connection-MS

The issue is that there must be a semicolon after the colon at the end of odbc as in jdbc:odbc:;Driver= . This made sense after reading the Oracle documentation on the JdbcOdbc bridge which states that the syntax is jdbc:odbc:dsn; attributes....... Since we are not supplying a DSN, then we need to end with ; before adding attributes.

I am showing below the tests I ran with different connection strings on a Windows 7 Ultimate 32bit machine:

        driver= (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        //jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=  does lookup to ODBC.ini to find matching driver


            try {
            connstr= "jdbc:odbc:;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn= DriverManager.getConnection(connstr, "", ""); 
            stmt= conn.createStatement();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:;Driver={Microsoft Access Driver (*.mdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn1= DriverManager.getConnection(connstr, "", ""); 
            stmt1= conn1.createStatement();
            dbmeta1=conn1.getMetaData();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:MS Access Database;DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn2= DriverManager.getConnection(connstr, "", ""); 
            stmt2= conn2.createStatement();
            dbmeta2=conn2.getMetaData();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn3= DriverManager.getConnection(connstr, "", ""); 
            stmt3= conn3.createStatement();
            dbmeta3=conn3.getMetaData();
        }
        catch (Exception e){}

stmt1 and stmt3 are null since the connections are null. stmt and stmt2 work. stmt2 uses a connection string I found in the documentation for IBM Tivoli. It works because "MS Access Database" is a valid title in the ODBC registry as a User DSN on my computer.

Note that this will cease to work upon upgrade to Java 8, as the Sun/Oracle JDK-bundled JDBC-ODBC Bridge has been removed. — TallTed, Apr 02 '14 at 17:05

score 0 · Answer 3 · answered Jan 04 '13 at 13:03

0

JDBC connection string start with jdbc: like:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\\newfolder\\db11.mdb

answered Jan 04 '13 at 13:03

Bhavik Shah

5,125
3
23
40

using the same command but giving the exception Data source name not found Exception – Adesh singh Jan 05 '13 at 09:50
@Adeshsingh : do you have db11 at the specified location and check the extension whether it is mdb – Bhavik Shah Jan 07 '13 at 08:30
ya i have the database on the same location and the error is related to DSN not the database location please help i m stuck badly here. – Adesh singh Jan 08 '13 at 04:46
@Adeshsingh : are you sure that the db extension is mdb and not accdb? – Bhavik Shah Jan 08 '13 at 11:21

creating jdbc odbc connection without creating the DSN in MS ACCESS

3 Answers3