Railo Access DSN on 64-bit Windows

I just wanted to post a quick note about this, as it was not the least bit obvious. I was helping to configure an old CFML application, which used an MS Access database, on a Windows Server 2008 RC2 (64-bit)/Apache 2.2/Tomcat 7/Railo 3.1 setup. This was being migrated from a 32-bit Windows server to a 64-bit Windows server.

Before I continue, let me first make a few suggestions. If you're building a new CFML application, I'd first recommend deploying (or at least coding to allow for deploying) to a Linux server using either MySQL or PostgreSQL databases. Linux servers are stable, secure, fast and cheap. MySQL and PostgreSQL databases can be used on just about any platform. If you prefer Windows servers (or have no choice), I'd still suggest MySQL or PostgreSQL databases, as they could be easily migrated to Linux in the future (Apache HTTPD also runs great on just about any OS). If you prefer to use (or must use) a Microsoft database, use SQL Server--not Access. There's an Express edition available, which is much more suited for a Web application database than Access, and there is much better 64-bit driver support.

Okay, so if you must go with an Access database on 64-bit Windows, the problem with using an Access database for a CFML application on a 64-bit Windows server... On 32-bit Windows it's quite simple to connect Railo to an Access database: use the Windows ODBC Admin to create a System DSN that points to your Access database file, and then create a Railo data source using the JDBC-ODBC bridge option. Unfortunately, on Windows Server 2008 64-bit, the ODBC Admin does not even show an Access driver option. If you Google around for a solution, you'll be tempted (as I was) to use the "secret" 32-bit ODBC Admin tool, which is located at %systemdrive%\Windows\SysWOW64\odbcad32.exe (the default 64-bit admin is at %systemdrive%\Windows\System32\odbcad32.exe--yes, Microsoft put the 32-bit tool under a directory containing "64" and the 64-bit admin under a directory containing "32" and both file names contain a "32"--nice). If you use this admin tool, you'll be able to create a System DSN for your Access database using a 32-bit driver, however, it will not work with the JDBC-ODBC bridge, because Railo/Tomcat a 64-bit application (running on a 64-bit JVM).

Finally I discovered that Microsoft has finally released 64-bit ODBC driver support, so the real solution is... Go here and download and run the AccessDatabaseEngine_X64.exe installer on your 64-bit Windows server. Now you can simply open the usual 64-bit DSN/ODBC admin tool (under Administrative Tools, I believe) and create a System DSN for your Access database (a driver is actually available there now). The Railo JDBC-ODBC bridge datasource type will now work.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Hatem Jaber's Gravatar Thanks Jamie, that's a great find!
# Posted By Hatem Jaber | 10/14/10 11:30 AM
Sean Coyne's Gravatar Thank you Jaime!
# Posted By Sean Coyne | 12/10/10 5:46 PM
Jamie Krug's Gravatar @Sean, @Hatem: Glad I could help!
# Posted By Jamie Krug | 12/11/10 10:53 AM
Leonard's Gravatar Excellent. Thanks muchly... I was getting worried that I would have to do some major re-work. I guess that's what I get for testing on a 32 bit machine!
# Posted By Leonard | 5/23/13 3:28 PM
Jamie Krug's Gravatar @Leonard: Happy to share. Cheers.
# Posted By Jamie Krug | 5/23/13 5:20 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.2.002. Contact Blog Owner