CF: Connecting 64-bit Coldfusion to 32-bit MS Access databases

First off, don't ask me why you would want to do this, just go with the flow.
In other words, let's not get bogged down on how you should not put anything on Microsoft Access anyway and accept the fact that there is still many applications that are perfectly happy with it ;o)
The problem has existed for a while but it dawned on me more clearly as I was sitting in on a hands-on beginner CF class at NC Dev Con and several of the participants immediately ran into this with their brand spanking new, high powered, Windows 7 64-bit computers.

So it seems more common that you would want to take the 64-bit edition from Adobe for a spin. But as soon as you attempt to connect to anything 32 bit you receive non-descriptive errors such as:

"Unable to update the NT registry. Variable DRIVERPATH is undefined."

Maybe dropping those options from the list of available drivers would have been wiser and less frustrating for users. Something for Adobe to ponder about I guess.




Microsoft does not have any (as of this writing at least) ODBC drivers for 64-bit Microsoft Access; in general, not much ODBC activity in a long time from the Microsoft camp. So, you might be well able to load up Office 2010 in 64 bit format; using the resulting databases for your projects, however, is a fairly complicated tasks.

As with all workarounds there are many steps to complete, so here we go:

1.) Download and Install 64 bit SQL Server 2005 Express Edition. You cannot use SQL Server 2008 Express edition, so don't try. It is lacking the needed OLE DB Provider.

2.) Download and Install Management Tools

3.) Download and Install Microsoft SQL Server JDBC drivers

For steps 1 through 3 here is a nice tutorial by Steve Brownlee - Fusioncube

4. ) Copy your MDB somewhere, e.g. c:\temp

5.) Created Linked Databases using Microsoft Jet 4.0 OLE DB Provider

a) Linked Server Menu. Right click on the Linked server node and then click on "New Linked Server..." sub-menu option:



b)linked server dialog. Product name can be anything you like. Data source should point to your access file:





6.) Create DS in CF Admin

a) create type other


b) specify data source connection properties




7.) Create sample code for CF. Your query code has to be changed, so you might want to create a variable to hold your prefix to your tables in case you change from 64 bit to 32 bit and vice versa.

Note that you have to use the four part syntax to query out of a linked MS Access to SQL database. You are just omitting two parts, there is also another syntax possible : Openquery(Linked_Server, 'Query') which I will not dig into.



To specify a query table in your access database you start with the name you have entered in SQL Server while the creating the link. In my case this is "Northwind". This name is followed by three periods (...) and, then, by the actual table name:
e.g. : NORTHWIND...EMPLOYEES

Here is my example code:
HelloNW.cfm file:

<cfquery name="selNorthwindEE" datasource="Northwind">

SELECT *
FROM NORTHWIND...EMPLOYEES

</cfquery>

<cfdump var="#selNorthwindEE#">

8.) Enjoy





Cheers,
B.