Microsoft Access 2007 and ColdFusion 8
With Microsoft Office 2007, Microsoft strikes again and implements a new format for their Access database standard. I do not recommend using Microsoft Access databases for production applications. In fact, I believe you will pay, sooner or later, for adopting such a practice. Nevertheless, it is useful for testing or development at times, and more importantly, you may be in a situation like me, where there are databases that you do not “own” or control but your ColdFusion apps must use them.
That said, please note that the drivers that come with CF8 do not support Access 2007. This is mentioned in the ColdFusion 8 Release Notes under #69495. The solution is nicely described right in the release notes. Download and install the Data Connectivity Components for Office 2007, set up a Windows ODBC connection, and use ColdFusion’s ODBC socket driver to connect to the Windows ODBC connection. A bit irritating that all of this has to be done, but like I said at the outset, these irritants are the price you pay, among other things, when using Access databases.
Another point to note! You thought you were done, but no, Microsoft has more joy in store for some of us. The Office 2007 drivers do not support Windows 2000. So if your server is still sitting on a Windows 2000 installation, the final word is either (a) No Access 2007 for you, or (b) Time to upgrade to Windows 2003 SP1 or later.
UPDATE: Definitely take note of the comment by Oğuz below that describes using JDBC along with the connectivity engine from Microsoft to create the datasource without having to use an ODBC socket.
March 4th, 2008 at 3:06 pm
You can use JDBC to connect Access 2007.
- Select “other” for DSN in CF admin,
- JDBC URL: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=c:/od/test.accdb;”;
- Driver Class: sun.jdbc.odbc.JdbcOdbcDriver
But this config does not support Unicode as expected.
March 4th, 2008 at 4:07 pm
Oğuz, THANK YOU for that. I’m not using Unicode anyway on these databases, and I really don’t want to use the ODBC Sockets. I’ll give this a shot soon.
January 14th, 2009 at 1:16 pm
I have tried the instructions above to no avail – when i open the odbc control panel all that shows up is a driver for sql – and i have tried to use “other” in the cf administrator as suggested and that doesn’t seem to work either – please someone help
February 26th, 2009 at 9:37 am
A good point to note: usage of the JDBC described by Oğuz above does still require the installation of the connectivity engine from Microsoft as described in my post.
October 7th, 2009 at 1:17 pm
Dumb Question: Does the ODBC Socket option support Unicode if your database uses Unicode?
October 7th, 2009 at 3:32 pm
Good question. Considering it’s just a pass-through socket, I’d imagine it would support Unicode if the target datasource supported Unicode. But I have not tried or tested this.
October 31st, 2009 at 3:00 am
I am new to this.
I am assuming the data connectivity component is installed on the CF server.
What do you mean:
set up a Windows ODBC connection (where?), and use ColdFusion’s ODBC socket driver to connect to the Windows ODBC connection (when?).
January 23rd, 2010 at 6:44 am
The process Oğuz works well.
July 12th, 2010 at 7:16 pm
Did not work for me either. I followed the instructions to the letter and I get the following error in CF Admin:
Connection verification failed for data source: test
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The root cause was that: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
August 23rd, 2010 at 12:50 pm
Just wondering if anyone has also tried this for converting existing ODBC connections to an Excel file that is now Excel 2007 instead of Excel 2003.
TIA