Connecting to an Access 2007 Database Using ColdFusion

The 2007 version of Microsoft Office introduced new file formats for Word, Excel, and Access.  If you need to connect to an Access 2007 database file, one with the "accdb" file extension, you will not be able to use the Access database drivers bundled with ColdFusion.  These drivers are designed for the previous Access file format which used "mdb" as a file extension.  In this post I will walk through creating a ColdFusion data source for an Access 2007 file.  This process was tested on a Window 2003 server using ColdFusion 9.0.1 on a 32-bit machine.  


Prerequisites


1. A ColdFusion installation which includes ODBC the optional services.

2. The Office 2007 System Driver package should be installed on your server.  This will install the ODBC drivers for Access 2007.  This may not be required if Office 2007 has been installed on the machine.
http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en


You must set up your data source twice.  First an ODBC connection must be created in Windows, and second a data source must be created in the ColdFusion administrator.


Create the ODBC Connection in Windows


1. Open Control Panel > Administrative Tools > Data Sources (ODBC).


2. Select the System DSN tab.


3. Click Add...

4. In the Create New Data Source window Select Microsoft Access Driver (*.mdb, *.accdb), then click Finish.


5. In the ODBC Microsoft Access Setup window fill in the Data Source Name, then click Select... 


6. In the Select Database window browse to the .accdb file you wish to use, then click OK.


7. In the ODBC Microsoft Access window click OK.


8. In the ODBC Data Source Administrator window click OK.



Create the Data Source in ColdFusion


1. Log into the ColdFusion administrator.

2. Open the Data & Services > Data Sources page.


3. Enter a Data Source Name and for Driver select ODBC Socket, then click Add.


4. On the Data & Services > Datasources > ODBC Socket page select your System DSN from the ODBC DSN list, then click Submit. 


Conclusion
There a few caveats to be aware of.  First, I have not tested this process with a password protected database.  Second, Microsoft's Office 2007 drivers are not intended for use in a service or web application.  I would not recommended the use of Access in a production system.