A few weeks ago I had to assist with migrating a customer with a large amount of Access databases (over 60) to a new server. Turns out their new server was completely 64-bit (Windows and ColdFusion) and I didn’t find out about the Access databases until after the server was completely deployed. Had I been involved earlier I would have put a stop to that. However, I had to make Access play nice with ColdFusion, here’s how I did that.
The first step is to open the 32-bit ODBC Datasource manager in Windows, on this server it was at C:windowsSysWOW64odbcad32.exe, where I had to add a system DSN for each Access DSN I was configuring.
As you can see there are a large amount of System DSNs for each database, we’ll be setting up the DSN named ‘blank’ in this post.
After you have a System DSN created for each DSN you need in ColdFusion, you can start adding them to ColdFusion. As you can see in the image below we set the data source up exactly as we did in the Windows ODBC manager, same name and path.
Now, when you hit Submit you’re going to get a very ugly error:
Unable to update the NT registry. Variable DRIVERPATH is undefined.
Don’t fear, the data source is now available for ColdFusion to use. Now, why didn’t I just use an ODBC socket? Well, you simply can’t – when you go to create an ODBC socket in the ColdFusion Administrator it generates a drop down of 64-bit System DSNs and won’t show you the 32-bit Access DSNs (see the Additional Reading section).
Remember, you’re milage will vary on this and you should be converting those Access databases over to SQL Server or MySQL 
Additional Reading
Why my 32 bit applications cannot see the ODBC DSNs that I created on my 64 bit machine ?
No related posts.
Brent, So adding a 32 bit System DSN named the same as the DSN you are setting up in CF and ignoring the reg error does the trick? That is a wonderful solution to something that has befuddled me a number of times. Well done!
Yes, I should preface, this works great on Windows 2008, but didn’t seem to have the same effect on 2003. I have a process for 2003 I need to get posted.
Just to say thanks for the post – the reason I’d migrated a client was specifically to upgrade the database, but I hadn’t expected any problems with using the Access DB during the change. I didn’t receive the scary NT registry message though (2008 webserver R2 & CF 9 – and why the reference to NT, it’s a bit like seeing the Macromedia message when the CF service is starting up…!)
Brett – ok… now I’m trying to use your fancy pants method on win2003 64bit. I can install the DB – but I get “unable to retrieve message from backend ODBC driver” when I try to use it. Any ideas?
Mark,
You’ll need to export the following registry key – HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI (make sure only that branch gets exported. Then open the .reg file in a text editor and replace any references to HKEY_LOCAL_MACHINESOFTWAREODBC over to HKEY_LOCAL_MACHINESOFTWAREWOW6432NODEODBC (I count 4 instances when I do this), save the .reg file and then import it into the registry. The ColdFusion Administrator may not work fully on the Access DSN, but you should be able to query the DSN through your code then.
Works perfectly for windows7 64bit, coldfusion 9 and office2007 combination. Many thanks
Mark,
Can’t seem to get it to work … I can connect great to my Oracle, but I need to set-up an Access to for importing some data …
I’m running CFMX 6.1 J2EE set-up on Tomcat 5.5 on a Windows 2008 (yeah, old school CF set-up, but does the job).
thanks a lot for the hint
after months of not being able to set microsoft access db on our coldfusion 9 server this helped out and solved the issue.. the error on the cf admin still there but works by using the gui at
C:\Windows\SysWOW64\odbcad32.exe
Thanks for posting this! I’m in the process of moving to a new Server (200864bit w/CF9 64bit) and this was my last stumbling block. It worked perfectly for me.
terrific! thank you for the post!