Using JDBC to connect to Microsoft Access database

I’m doing a training tomorrow for which I needed to use a demo Microsoft Access database.
However, my laptop does not have ColdFusionMX 7 ODBC Server and ODBC Agent installed as Windows services. So, each time I try to setup a Microsoft Access datasource, I get a message:

The ColdFusion MX 7 ODBC Server service is not running or has not been installed.
You may also use the “MS Access with Unicode” driver to connect to MS Access datasources.

As I didn’t feel like manually installing the ODBC agent and server, I wondered if there was a way to simply use JDBC to connect directly to the Access database.
And there is.In ColdFusion Administrator, create a datasource of the type “other”. And use the following settings subsequently:

JDBC URL:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=[Full path to your database file];DriverID22;

Driver Class:

sun.jdbc.odbc.JdbcOdbcDriver

I prefer to always specify the Driver Name. It helps later to identify the database type from the datasource listing page. You can skip this if you don’t give a toss.

Drive Name:

MS Access via JDBC

Username, Password:

If the database requires any username, password, specify those.

Submit and and that’s it.

10 thoughts on “Using JDBC to connect to Microsoft Access database”

  1. Cancel my earlier comment.

    CF on Mac does not ship with sun.jdbc.odbc.JdbcOdbcDriver. So that won’t work.

    So now, on to figure out what will get a Microsoft Access DSN going on CF.

  2. I had a query built in ms-access that would not execute from CF with the access drive.
    But changed to this method and it worked.

    Thanks

  3. I’m not sure what the hold-up is… maybe they have re-thought their stance on how this is going to actually make the company any money. Or perhaps their lawyers pointed out the liability of providing agents a platform to stick their feet in their mouth. Whatever it is, it’s hardly something I’d claim as being "Well done".
    http://www.jebshouse.com

Comment on this blog post...