Thang Pham Thang Pham - 3 months ago 13
Java Question

Tomcat 7: How do I specify Database resource when making connection to Microsoft Access

I know how to specific datasource connection pooling to a mysql server in Tomcat 7, add

<Resource type="javax.sql.DataSource"
name="jdbc/TestDB"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mysql"
username="mysql_user"
password="mypassword123"
/>


to my
META-INF/context.xml
,


  1. Can anyone show me how to do this to
    Microsoft Access
    ?

  2. Do I need a JDBC - ODBC bridge to make connection from my Java web app to Microsoft Access?



UPDATE: This is my attempt, but I ran into
SQLException


<Resource type="javax.sql.DataSource"
name="jdbc-odbc/qtl"
maxActive="100"
maxIdle="30"
maxWait="10000"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=/Users/KingdomHeart/resources/db.mdb"
/>


When I do this, I got
WARNING: Unexpected exception resolving reference
java.sql.SQLException: sun.jdbc.odbc.JdbcOdbcDriver


UPDATE 2:: I tried to write a separate program in effort to make connection to Microsoft Access db. I got
java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
Here is my code. It must be that I need a
jar
driver in my class path, do I not?

public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String fileName = "/Users/KingdomHeart/resources/MyTable.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database += fileName + ";DriverID=22;READONLY=true";
Connection con = DriverManager.getConnection(database, "", "");
System.out.println("here");
} catch (SQLException ex) {
Logger.getLogger(TestJdbcOdbc.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(TestJdbcOdbc.class.getName()).log(Level.SEVERE, null, ex);
}
}

Answer

I'd use a URL like this:

jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\path\\your-access-database.mdb

The JDBC driver class is sun.jdbc.odbc.JdbcOdbcDriver (at least it was when I last used it; that was back in 2004).

I prefer this style because it eliminates the step of creating an ODBC data source. It keeps the precise location of the file inside the Java deployment.

Yes, you need the JDBC-ODBC bridge (unless you bought a commercial JDBC driver).

You should be aware of the pitfalls of using Access in a multi-user environment:

http://msdn.microsoft.com/en-us/library/aa167840(v=office.11).aspx

My personal recommendation would be to use MySQL or PostgreSQL instead of Access.

Here's something else you might want to read about setting up a JNDI data source on Tomcat:

http://craicpropagation.blogspot.com/2009/02/how-to-use-same-jndi-resource-name-on.html