Access DSNs in 64bit ColdFusion

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:\windows\SysWOW64\odbcad32.exe, where I had to add a system DSN for each Access DSN I was configuring.
Windows system DSNs
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 ?

18 thoughts on “Access DSNs in 64bit ColdFusion”

  1. 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!

  2. 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…!)

  3. 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?

  4. 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.

  5. 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).

  6. 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

  7. Thank you so much for the article, I wasted a previous hour trying to fix this!

    What matters here is the sequence. It only worked when I deleted the DSN from CF Data Sources and started with the mentioned sequence (ODBC dsn first, then CF dsn) – it only verify from the CF data sources list as you mentioned.

    PS: I was on Windows 7 64bit, CF9.0.1 and Access 2007 (.mdb) file. Also, I used the latest Access driver listed on my ODBC (jet one), 2011

    Cheers,
    John

  8. I have followed all of your instructions and by all accounts they should be working. I am running into an issue with the fact that my Access data sources are located on a network drive. These steps do not seem to work with a network location for storage. Does anyone have any advice on how to get these Access databases working from a network location?

    Thanks!

  9. This will only work if you can add the data sources in the CF administrator. Unfortunately, in CF10, it no longer appears to be possible to do that, at least not for Microsoft Access.

Leave a Reply

Your email address will not be published. Required fields are marked *