Solving the Access Driver Problem on ColdFusion 8 - 64 bit
First off, let me say that I love ColdFusion 8 on the 64 bit JVM. If you have a data driven site that requires extra processing power you should definitely move in that direction. However, there is one thing that really causes some consternation about the platform - the lack of Jet drivers for MS Access. I know I know, I have spoken with open disdain about "using Access in production" and in the words of Dan Quayle, I stand by all my misstatements. Still, there is one thing that I do use Access for in production - as a portable export utility.
Using the proxy technique I describe in my post counterintuitively titled Using a DSN Connection for Connectionless Access I have several applications that crunch numbers or develop report data, then file them away in tables in an MDB File which is zipped up for download or emailing. It works well and Access is so much more useful than Excel for some savvy corporate analysts. Now along comes 64 bit Windows and suddenly there are no (zip, nada, zilch) Jet drivers for connecting from Coldfusion.
Luckily, I have my wits about me. Also luckily I have a 32 bit server at my disposal as well (it means "available to use" - I'm not implying there is a server near my kitchen sink). So I came up with this clever solution.
Set up Access
I configured the 32 bit CF server to host the Access DB and I set up the DSN. For example, let's say I added an Access DSN to the 32 bit server and named it "AccessProxy".
Install Remote Web Service CFC
Next, I installed a CFC to act as a proxy. It's a pretty simple piece of code.
<cffunction name="runQ" returntype="query" access="remote">
<cfargument name="dsn" type="string" required="Yes"/>
<cfargument name="qString" required="yes">
<Cfset var qry = ''/>
<cfquery name="qry" datasource="#dsn#">
#preservesinglequotes(qString)#
</cfquery>
<cfreturn qry/>
</cffunction>
</cfcomponent>
I also added code to restrict the running of this function to specific IP address calls - that way it could only be called from one of my internal servers.
Add a Web Service With a Duplicate DSN Name
This is where I get really clever. Remember, I'm trying to get some existing code to work on the 64 bit server and I want to avoid wholesale code changes. I do not want to replace all that Cfquery code with something completely different. So I took a look at the cfquerys that were pointed to the MS Access DBs and found the name of the "datasource" attribute. Using this attribute I went to the CF administrator on the 64 bit server and I added the web servivce WSDL file as a registered web service with an alias matching the datasource.
For example, if the datasource name was "AccessProxy", I opened the CF administrator, navigated to "data and services" and clicked on "Web services" and entered something like this:
Now, instead of a datasource named "acessProxy" I have a web service alias named "accessProxy".
Simple Custom Tag
What's next? Again, the goal is to avoid too many code changes. I will have to make some changes, but I want it to be simple and straightforward. So my next step is to create a custom tag to replace the "cfquery" tag.
<cfparam name="attributes.datasource" default=""/>
<!--- query to return --->
<cfparam name="Attributes.name" default=""/>
<Cfif thistag.executionmode IS 'end'>
<!--- trap the query string --->
<cfset qString = thistag.generatedcontent/>
<!--- create my WS obj --->
<cfset accessObj = createobject("webservice","accessProxy")/>
<!--- pass the query string --->
<Cfset qry = accessObj.runQ(dsn=attributes.datasource, qString=qString)/>
<!--- set the query back to name --->
<cfset caller[attributes.name] = qry/>
<!--- make sure nothing goes to the output buffer --->
<Cfset thistag.generatedcontent = ''/>
</CFIF>
Let's review a couple of things. The name of the web service and the DSN need to be the same on both servers. It is serving as an alias for my proxy and as a cfquery attribute on the 32 bit side. That's important because it will help me avoid too many code changes. It's also important that this custom tag go into the customTags directory - or a custom directory specified in CF Admin. Otherwise you will have to copy it around to folders where you want it to run. Finally, if possible you will want to name the file "query.cfm" - again to minimize code changes (as you shall see in a moment).
Last Step, Alter the Cfquery
Now that I have my web service and custom tag in place, what is the final step? What changes do I need to make to my query to get it running through the proxy web service? The only change you will need to make is the addition of an underscore between CF and query. Your old Access query looked like this:
SELECT *
FROM users
WHERE username = '#userName#'
</cfquery>
...And your new query looks like this:
SELECT *
FROM users
WHERE username = '#userName#'
</cf_query>
One Rather Large Caveat
You may have spotted it already. If you are using CFQUERYPARAM (and you should be) then you will have more code changes to make. In fact, you may wish to further enhance this proxy process and utilize an argument array or something for security reasons. In this case all my stuff is "export" stuff and quite old, and therefore (and this is not an excuse) it had very few cfqueryparams to contend with.
Whew! This entire process took longer to write up as a post than it did to create. Now I will await the judgment of my savvy and too smart for their britches readers who will explain to me how I might have done it if I had been as clever as they. Fire away :)
16 Comments
-
Great idea. We need MS Access at least to import/export data. Note that there is a small error in your code (createobject("webservice","#attributes.datasource#") must be createobject("webservice","accessProxy"). I tested it on my own 32-bit machine and it works and so I am now ready to move to full CF/windows 64-bit. We just lack a smart and generic import/export template to exchange data between MS Access and MySQL5. Thanks -
@pierre,
thanks for the correction - I edited the post. I was wondering if anyone was even seeingt his post since there were no comments. I thought it was pretty nifty - but I suspect it's a bit of a niche problem eh :) -
No Mark, it is a very important problem. My next laptop will be a 64-bit Windows 8GB Vaio Z with SSD and I use MS Access to prototype so I will have to move to MySQL5, but my customers only send MS Access files and I have to send them MS Access files and I have a lot of MS Access databases. From yesterday, I am writing a generic template (juste give datasource and table name) to write specific templates to import MS Access databases on a 64-bit MySQL Windows using your CF_query tag. We cannot stay away from the 64-bit...
-
I agree this is a bit of a pin in the ass for prototyping. I am on a 64bit laptop and so I'm looking for an Access->Derby converter. If anybody knows of one, shoot me an email at chip at temm dot net? -
Eventually I got my Vaio SSD 64-bit Vista. I tried to install CF9 32 bit on a full day (10 installs looking at a lot of usefull links): no way... I try Apache 2.0 also: no way. I installed CF9 64 bit and it was OK at the first try... So no more MS Access links on my new computer except the one proposed above... or your are to be very good at CF installers...
-
Thanks for sharing this. It initially has helped me to get past my access problem, which was similiar to yours. However, I implemented a 32-bit system (to support access) through a VM on the same 64-bit physical machine. However, it appears that the accessProxy will fail if I try to consume it via another webservice that I use for Ajax grid support. The only difference I can see is that one is called by a <cfinclude>, which has the application scope, while the other is is a url, which does not. I'm not quite certain if the wsdl plays a roll in the error (Axis) that I am seeing. It faults on the statement <Cfset qry = accessObj.runQ(dsn=attributes.datasource, qString=qString)/> of your custom cfquery tag. Any suggestions would be appreciated. -
I had the same problem as Jim above. I'd really love to get this working as, of course, we migrated our services to 64-bit without the commiserate 64-bit M$ Access driver... -
@Jim and Keith,
If your problem is related to accessing the CFC directly via AJAX, is it possible you are running into the application scope issues. I describe just such a conundrum in this post on using a shared lyla captcha with a suite of sites on the same server.
http://www.coldfusionmuse.com/index.cfm/2009/9/16/...
Perhaps it will help. Good luck. -
the ideal solution , use a type 4 jdbc driver on your 64bit server .
http://www.hxtt.com/access.html -
@Bob,
If that works I'll send you a case of girl scout cookies :) -
@Mark
I have 4 JDBC Type 4 drivers on my 64bit windows 2003 server with CF8 Ent
1- JTOpen for accessing AS/400
2- Oracle Native JDBC
3- HXTT for DBF files
4- HXTT for MS Access
and they all work like a charm .
waiting for my box of cookies :) -
@Bob, @Mark
Were you guys able to successfully install the HXTT drivers? I'm going through their documentation and it's making my head spin. Is there a quick and easy way to install these drivers and use them in ColdFusion? -
@David
you don't install JDBC drivers , just copy them to a folder , and add the jar file name with full path to JVM configuration in CF Admin. -
@Bob
Where do I add the jar file name with path in the JVM config? In the "ColdFusion Class Path" or "JVM Arguments" area?
On the HXTT site it sais it's trialware (30 days), so if I add the "Access_JDBC30.jar" to the JVM like you wrote it will expire and stop working after 30 days? -
For what it's worth, I bought the HXXT drivers and my ISP installed them and they work fine. -
Has anyone ever tried using the Microsoft Access Database Engine 2010?
http://www.microsoft.com/downloads/details.aspx?fa...
Sites to Watch
Aggregators
RSS
