Java: MS Access and JDBC connectivity

I want to connect with MS Access from my Java code.

How to do this?

I have written the following code:

import java.sql.*;
public class Test
{
 public static void main(String[] args) 
 {
   String dataSourceName = "test";
   String dbURL = "jdbc:odbc:" + dataSourceName;
   try { 
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection con = DriverManage开发者_运维知识库r.getConnection(dbURL, "ify","ify123");

     Statement statement = con.createStatement();
     ResultSet rs = statement.executeQuery("select * from emp");
     System.out.println("hi");
     while ( rs.next() ){
       System.out.println(rs.getString(2));
     }
   }
   catch (Exception err) {
     System.out.println( "Error: " + err );
   }
  }
}

The problem is that I'm still not able to coneect with the database. What I might be doing wrong?

This might be a driver manager registration problem.

You can use alternate statements as:

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver()); Connection conn = DriverManager.getConnection(....);

This will do.

Best of Luck!

Enable SQL trace log in ODBC Administrator (there is Trace tab). Run your program and see what is logged in trace log. My test Jython application that uses Access db from User DSN logs something like:

Connecting:

....
jython c68-f18  ENTER SQLDriverConnectW 
    HDBC                053C1CC0
    HWND                00000000
    WCHAR *             0x74604EC8 [      -3] "******\ 0"
    SWORD                       -3 
    WCHAR *             0x74604EC8 
    SWORD                        2 
    SWORD *             0x00000000
    UWORD                        0 <SQL_DRIVER_NOPROMPT>

jython c68-f18  EXIT  SQLDriverConnectW  with return code 0 (SQL_SUCCESS)
    HDBC                053C1CC0
    HWND                00000000
    WCHAR *             0x74604EC8 [      -3] "******\ 0"
    SWORD                       -3 
    WCHAR *             0x74604EC8 
    SWORD                        2 
    SWORD *             0x00000000
    UWORD                        0 <SQL_DRIVER_NOPROMPT>
....

Fetching data:

jython c68-f18  EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
    HSTMT               053C2970

jython c68-f18  ENTER SQLGetData 
    HSTMT               053C2970
    UWORD                        1 
    SWORD                        1 <SQL_C_CHAR>
    PTR                 0x034DB270 
    SQLLEN                     6
    SQLLEN *            0x009FF620

jython c68-f18  EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
    HSTMT               053C2970
    UWORD                        1 
    SWORD                        1 <SQL_C_CHAR>
    PTR                 0x034DB270 [       5] "WOLZA"
    SQLLEN                     6
    SQLLEN *            0x009FF620 (5)

Analyze that log and you will probably find some info about errors.

Is your table empty? Do you get any error messages?

Did you add code like the following to see if the connection was made:

ResultSet rs = stmt.executeQuery( sql );
ResultSetMetaData md = rs.getMetaData();
System.out.println( md.getColumnCount() );

Edit: Have you tried connecting without using a DSN:

String url = 
    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/directory??/???.mdb";