Access on 64bit ColdFusion? Yes we Can!!
First, let me make sure I do not get off on the wrong foot here. I am most explicitly not saying that you should be using Access on a production server with heavy traffic as a regular application data source. In my opinion Access should be limited to the smallest use possible on web servers. It's fine if you have a small intranet application to keep track of equipment or handle tickets among 4 or 5 people - although why you wouldn't at least move to MySQL or MSSQL express is puzzling. However (and the Muse can't be clearer than this) if you are using Access to backstop a high traffic ecommerce site or content management for an important customer then shame on you. It's a problem you will have to solve - maybe not today, maybe not tomorrow... but someday and for the rest of your product life cycle.
Having said all that you might remember my post on setting up an Access Proxy. The problem I was solving in that case was quite specific. I was using Access as an export format for an enterprise application. Users would run a very large report and then have the choice to export it to Excel or Access for offline viewing or mining. As far as I'm concerned this is an appropriate use of Access - as an offline data mining tool. My process basically copied the file to a unique location and used the Access Passthrough method to populate it, zipped it up and sent it to the users browser.
When I moved to 64bit ColdFusion I was forced to come up with an elaborate proxy service to insure that the process continued to function as usual. This was because I could not figure out how to get Sequelink (those funky "ODBC Server" and "ODBC Agent" services that install with ColdFusion) to talk to ColdFusion any more in the 64bit environment. Recently however, Brent Fry delivered this excellent post as well as provided me with some additional info for the windows 2003 server platform. So I now have a new process that is capable of synching up Sequelink with ColdFusion.
There's nothing wrong with Brent's post of course, but I have a desire to codify this in my own words. Plus I know I'll need it again and if I don't write it down for myself I forget where I found it the last time (no offense to Brent and cfexecute.com and with all credit due!). With that in mind, here's the Muse version of Access on 64bit ColdFusion with a little "under the hood" stuff thrown in.
Finding the Jet Driver
To start we need to locate those pesky 32 bit drivers. It's important to know that the issue with the venerable JET driver (the long-in-the-tooth driver we have using for decades to access MS Access files) on 64 bit Windows is not that it doesn't work on the 64bit OS. It's simply that there is no 64 bit version of Jet. When you go to "Administrative Tools" and click on "Data Sources (ODBC)" on a 64bit Windows box you are seeing only the 64bit drivers. It's actually really goofy. The link in administrative tools points to %SystemRoot%\system32\odbcad32.exe. The name of that file would seem to indicate 32 bit drivers to me, but in reality what you are seeing is the 32 bit control panel applet for managing system DSN's. The list of drivers available (if you click on Add) will be only 64bit drivers. By default this means you will likely see only MS SQL server (installed by default) and any other 64bit ODBC driver you have installed.
Here's the catch. Windows 64bit is capable of running 32bit software. You know this if you think about it. In fact the odbcad32.exe file above is a 32 bit control panel applet for managing 64 bit drivers. So the trick is to figure out how to manage 32bit drivers on your system. The good news is that the old version of the ODBC manager (the one that handles 32 bit drivers) still exists on your 64bit machine. Take a look in the %systemroot%/sysWOW64/ folder. You will see a version of the same file - odbcad32.exe. Open this control panel applet and you will discover that it actually manages the old list of 32 bit drivers. I know it's convoluted - a file in "system32" called odbcad32.exe manages 64 bit drivers while a file in the sysWOW64 folder manages 32 bit drivers. If you need to know, the "sysWOW64" folder stands for "Windows on Windows 64" - meaning plain old Windows 32 bit aps running on the Windows 64 bit platform. Now some Muse readers just lit up with delight at the new information while other's had their eyes glaze over with a dull light from TMI. Snap out of it and let's keep that tool up while we talk about the next thing - the Sequelink server.
Sequelink Server Fun
In all MX version of CF (starting way back with version 6 when Ben Forta was still a lad and Sean Corefield was still tending sheep) MS Access has been handled using the "Coldfusion MX ODBC Server" and the "Coldfusion MX ODBC Agent". These 2 services are running a version of a product called "Sequelink Server" from Merant. Under the hood the Sequelink server sets up ODBC links to the Access file and provide a socket interface for JDBC. CF makes JDBC calls to the socket and Sequelink translates the calls to ODBC, gets the result, retranslates to JDBC and sends the result back upstream to the JVM.
Incidentally this is one reason why ODBC and Access is even less of a good idea in ColdFusion MX than it was in ColdFusion 5. At least in ColdFusion 5 (running as native C++ code on windows) CF accessed the ODBC subsystem directly and used ODBC drivers. As soon as CF became a Java product an intermediate layer - a "bridge" layer - was introduced to make communication between JDBC and ODBC possible. This intermediate layer further complicates the use of ODBC and adds additional latency to the system already under pressure (Access never worked that great on CF 5 or any production platform for that matter - it's simply not a database "system". It is, rather, a file and driver pair). In any case this "bridge" layer is provided by the Sequelink server. If you look in the task manager it shows up as the swsoc.exe process.
Now putting two and two together we can assume that, since Sequelink uses a socket interface for CF to access and since the 32bit drivers do indeed exist on Windows 64bit - it stands to reason that it must be possible to get the two of them to work together. After all - Verity is 32bit and it cooperates with CF just fine on the same principle - a socket type interface. So this must be possible - right?
The Real Problem
The real problem here is actually pretty basic. It's not that the driver doesn't work or doesn't exist. The same 32bit drivers that have always worked will work with the same Sequelink server that has always worked. Instead the real problem is that the registry entries, driver paths and other "configuration" stuff under the hood is now in different places. The trick is to mirror the old configuration somehow.
The Fix - Windows 2008r2
Step 1:
The fix is different depending on whether you are running 2008r2 (which only comes as 64 bit) or Windows 2003 64bit. Let's start with r2 because the fix is easier for that platform. Remember the odbcad32.exe file in the /sysWOW64/ folder? Start with that little applet and click on the "system DSN" tab. Click on the "Add" button and you will see a list of drivers including the "MS Access" driver. Enter the same name for the data source that you intend to use in the ColdFusion administrator as the data source name. Browse to the Access file you are going to use and click ok. You should now see your ODBC data source in the list.
Step 2:
Next, log into the CFIDE Administrator and add a new "MS Access" data source using the same name as you did in the ODBC control panel applet. When you click "add" you will get an error message that says "Unable to Update the NT registry", but the data source will still register. To test, you can try running a simple query like this one:
SELECT now() AS dt
</cfquery>
The Fix - Windows 2003 Server 64bit
If you are using Windows 2003 things are a little trickier. Again it was Brent Frye pointing me in the right direction. What's happening in 2003 is that the registry entries for the 32 bit drivers are not precisely mirrored. If you try the procedure above on a 2003 64bit server you will find that the HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC node is NOT created - so Sequelink cannot find the file and driver. Instead the old style registry entry is created at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\. The entries are correct - they are just in the wrong place. What's needed is a little creative registry work. Please note - if you are reading this blog and have gotten this far you are likely an experienced developer or sys-admin. All the usual warnings about editing the registry apply here. Yes Virginia you can make your server completely useless if you don't know what you are doing. That said - here are the relatively painless instructions.
Step 1.a
After step one but before you log in to the ColdFusion Admin, go to the registry editor and find the entry at the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\*data source name* entry. For example, I added an system DSN called bob so I navigated to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\bob. Export this key to a .reg file and open it for editing in notepad. You should see something like this (with paths and dsn name reflecting your own entry of course).
Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bob] @="" "Description"="" "DBQ"="D:\\dbs\\uncles\\bob.mdb" "SystemDB"="" "UID"="" "PWD"="" "DriverId"=dword:00000019 "FIL"="MS Access" "DefaultDir"="D:\\dbs\\uncles\\" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bob\Engines] @="" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bob\Engines\Jet] @="" "PageTimeout"=dword:00000258 "MaxBufferSize"=dword:0000fa00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bob\Engines\Jet 2.x] @="" "PageTimeout"=dword:00000258 "MaxBufferSize"=dword:0000fa00Carefully change the first part of the path of each of the [HKEY_LOCAL_MACHINE...] entries to reflect this path instead:
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\Your finished script should look similar to this:
Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\bob] @="" "Description"="" "DBQ"="D:\\dbs\\uncles\\bob.mdb" "SystemDB"="" "UID"="" "PWD"="" "DriverId"=dword:00000019 "FIL"="MS Access" "DefaultDir"="D:\\dbs\\uncles\\" [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\bob\Engines] @="" [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\bob\Engines\Jet] @="" "PageTimeout"=dword:00000258 "MaxBufferSize"=dword:0000fa00 [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\bob\Engines\Jet 2.x] @="" "PageTimeout"=dword:00000258 "MaxBufferSize"=dword:0000fa00Save the file with the .reg extension and run it to insert these entries. Now Sequelink can "find" the files and proper drivers and you can add the data source using the ColdFusion admin (Step 2).
Conclusion
This is a fairly involved process and I would not want to implement it on a server with dozens of Access files. As always the Muse believes in minimal and judicious use of Access on production web servers. Still, if you absolutely need to get Access/Jet running in a 64bit Windows environment, it's nice to know it's still possible. Now, beloved Muse readers, a word of caution - please keep comments relevant to the topic. I'm not afraid of criticism or correction (as my readers well know) but I rather abhor the kind of conflagrations that sometimes occur when discussing MS products (or windows or mac or linux or adobe etc etc). Keep your comments civil and on point please. Let's make entries like this helpful. I often refer folks to my blog with the cavaet that "some of the best tips are in the comments", so help me make sure that stays true.
17 Comments
-
@alycen,
Keep working for me and I guarantee you WILL need it (ha!). -
Access is the Devil dressed like a beautiful woman! I am one of those that started my first web relationship with Access. She was dainty, simple, and inexpensive to get around with. Then, as my needs grew, she grew stubborn and starting throwing fits. Now I am entangled in a relationship that is all but almost impossible to get out of. With over 60 separate Access children I am struggling to move our relationship to a more unified understanding that is sure to be a better SQL ... -
Access 2010 is available in both 32-bit and 64-bit versions. Would there be a way to natively sync up the 64-bit version as a DSN with CF? I would assume it would use a 64-bit version of JET? -
Andy,
Although Access comes in a "64bit version" I haven't seen a 64bit driver. There's no MDAC install etc. I suppose you could put Access (the IDE) on your server and that would get whatever driver it uses on there - but that would be a kludge in itself.
Morever, Sequelink (at least the version installed) is 32bit and CF depends on Sequelinks listener. So I suspect that just finding a 64bit driver is only half the battle. -
Mark,
I should first admit that I skimmed quickly over a few parts ;-) That said, I had to deal with a similar issue for a client on Railo not too long ago. In short, I figured out how to create a system DSN on the Windows 64-bit server, and then used the normal Access DSN setup from the Railo Admin. I'd imagine this should work fine with Adobe ColdFusion or Open BlueDragon as well, but can't say I've tried.
Anyway, I documented it in a blog post, here:
http://ur1.ca/22iex
Cheers,
Jamie -
Hey Mark, good to see you back to podcasting, well, at least adding audio. (Brian Meloche had clued me in to this.) Even with the direct link, I almost missed the audio, as it's kind of buried in the page here with no mention of "podcast". You also didn't mark it in your podcast category.
I did drop the RSS feed for the blog into itunes and it found the audio, though. You may want to at least start tagging it as the podcast category, for those who already have or want to start following it. Welcome back.
BTW, the audio is a little scratchy, if you can tweak things a bit. I realize you may be trying to go "low impact" in not doing any editing, etc. Just sharing the feedback, while welcoming you back. I'll update your entry in the CF podcasts section of cf411.com. :-) -
Thank you for an excellent tutorial. I spent two days googling & reading everything I could about this issue and found your paper and Brent's the best.. Jamie's article was useful too, as it shed light on the big picture.
If it is helpful to anyone else, I would like to add that my W2K3 (64b, Enterprise) acts exactly like you say W2K8 works (ie, odbcad32.exe DOES create the WOW6432NODE entry in registry just fine, not sure why not for others using W2K3 64b, unless it has something to do with Enterprise version (?)). Also, I can tell you that Jamie's method, although slick as a whistle for some apps like Railo, doesn't create the WOW6432Node entry, so for Adobe CF 64b that method does not save any time ... at least on W2K3 64b, Enterprise.
Thank you everyone, I am humbled!
Byron -
addendum: my W2K3-64-enterprise is R2 ... maybe why it acts like your W2K8 R2 (?)
-
@Byron,
Glad I could help! Thanks for the input on 64b win2k3. -
This didn't work for Office 2007/2010 accdb files.
I spent a lot of time trying to get this to work on Windows 7 64 (I imagine it also works for Windows Vista 64) and it took a different form. However your help got me started.
Working with the ColdFusion 9 64bit environment, I had to install the Office 2007 32 bit drivers: http://www.microsoft.com/download/en/details.aspx?...
Then, I could add the entries using the odbcad32.exe and do the manipulations like you suggest above.
However, I had to change the registry entries for the 64 bit ODBC keys to reflect the Jet drivers from
"C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE14\\ACEODBC.DLL"
to
"C:\\PROGRA~2\\COMMON~1\\MICROS~1\\OFFICE12\\ACEODBC.DLL"
For anyone who follows your blog post and takes the time to understand, this will make sense! -
@Cliff,
Thanks for the follow up!! Your tip is much appreciated. I forgot that the driver had changed for office '10. Your comment will be of great help to those trying to make use of the new format.
-mark -
When I attempt to use your solution I run into the following error:
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
The Access data sources that I am trying to use are located on a network drive and are not physically stored on my web server. Do you have any advice how to get these DSNs working? -
I know this is an old post (2010, so from the CF9 timeframe). As I was helping someone with this issue on CF2016 (who had found this post and was having trouble), I wanted to share a couple of observations, given the last comment here otherwise was from from 2013.
First, some people were able to use this same approach into CF 10 and 11.
But then some stumbled because they did not have the CF ODBC services installed. I'll share briefly that there are ways to get that working (via some code that Adobe shared online, using the CF AdminAPI to run the installODBCservice method of the datasource.cfc).
Then as for CF2016, it seems most found that even the tips shared here did not work for them. Some found an easy solution by downloading the commercial hxtt JDBC driver for Access, putting its JAR into CF, and then defining an "other" DSN to use that.
Some readers may wish for still more detailon some of the things I have shared. I'm sure Mark (like me) would be reluctant to offer much more detail to folks on trying to get Access working for folks here in 2018.
The bell is tolling on using Access with CF (it's not even listed in the supported databases docs for CF, though it does remain in the CF Admin as a DSN driver type option).
Bottom line: it's time for folks to move to something else--any other "real" DBMS--such as mysql or the free SQL Server express, like Mark says in his first paragraph here.
Again, I'm just sharing this little bit extra in my comment here as the last comment was from 2013 (and did not indicate if it was even about CF10 or not, which had come out in 2012), and before that the last comment was from 2011.
Hope it may help some readers still finding this post years on. It's just one of many classic, long-tail posts here on the Muse site! :-) -
@Charlie I just installed CF2018 on my dev system. I checked the datasources drop down and MS Access is gone! It seems the bell has rung. -
Wil, that's interesting to hear. I have CF2018 on multiple machines, with Std, Ent, and Developer edition licensing, and all show it. (And I also had the CF ODBC Services enabled on one and not enabled on another. That had no impact.)
But even then, and doing the typical tricks (creating a DSN with the 32-bit ODBC control panel, and THEN pointing to the DB as an Access type here, it still would fail to work.
Certainly if someone is compelled to dig in to figure out of some combination of factors might work with CF2018 or earlier, have at it.
And Wil, you may be tempted to find when some DO and some do NOT see Access listed as an option in CF2018.
But again, all the vagaries about it just do spell all the more the pending end for CF support of Access.
Sites to Watch
Aggregators
RSS
