How to reference MS Access database in ColdFusion MX 7 on 64-bit Windows

New Here ,
Dec 14, 2008 Dec 14, 2008

Copy link to clipboard

Copied

I am migrating customers from:
Windows 2000 Server (fully patched)
Internet Information Services (IIS) 5
ColdFusion 5
to:
Windows Server 2003 Standard x64 Edition (fully patched)
Internet Information Services (IIS) 6 (running in 32-bit mode)
ColdFusion MX 7 (7.0.2) Standard

Unfortunately, some of these existing customers are still using Microsoft Access databases instead of SQL Server. Having all of these existing customers migrate to SQL Server is not yet practical. On the old Windows 2000 / ColdFusion 5 server, they are using OLE DB data sources to reference the MS Access databases using the "Microsoft.Jet.OLEDB.4.0" provider. None are configured to use ODBC drivers when referencing MS Access or SQL Server.

I have yet to figure out how to get ColdFusion MX 7 to:
- Create a data source that references MS Access databases on 64-bit Windows.
- Reference an existing MS Access data source created with the 32-bit version of the ODBC Data Source Manager on 64-bit Windows.

A bit of background information regarding MS Access on 64-bit Windows:
- At the time of this writing, Microsoft has not created/released 64-bit drivers for Microsoft Access and it's unlikely they ever will, for understandable reasons.
- Contrary to seemingly popular belief, the Microsoft Jet 4.0 Database Engine does exist on 64-bit Windows. See the following regarding version and file location information:
How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine
http://support.microsoft.com/kb/239114
- You CAN create MS Access based data sources within 64-bit Windows, but they have to be made using the 32-bit version of "ODBC Data Source Manager". I'm assuming these DSNs may only be used by 32-bit applications, but I have not tested that theory.

32-bit data sources:
ODBC Data Source Manager:
%SystemRoot%\SysWOW64\odbcad32.exe
Registry location:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
Default file DSN directory:
C:\Program Files (x86)\Common Files\ODBC\Data Sources

64-bit data sources:
ODBC Data Source Manager:
%SystemRoot%\system32\odbcad32.exe
Registry location:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
Default file DSN directory:
C:\Program Files\Common Files\ODBC\Data Sources

The 64-bit version of "ODBC Data Source Manager" (under Administrative Tools) only show "SQL Server" and/or "SQL Native Client" as options when creating new data sources, unless third-party software is installed that supplies additional drivers.

The 32-bit version of "ODBC Data Source Manager" (by default, there is no shortcut for this in the Start menu) shows the typical drivers seen on 32-bit versions of Windows, including SQL Server, SQL Native Client, Microsoft Access, Excel, FoxPro, Paradox, etc.

When using ColdFusion Administrator in ColdFusion MX 7.0.2 in 64-bit Windows 2003 (IIS is running in 32-bit mode to allow ColdFusion MX 7 to function at all), two problems are encountered when dealing with MS Access (and presumably other 32-bit drivers). In both cases, ColdFusion is trying to reference the registry location for 64-bit data sources instead of 32-bit:

1. Creating a new data source within ColdFusion Administrator:
Data Source Name: whatever
Driver: Microsoft Access
CF Data Source Name: whatever
Database file: (physical path to whatever.mdb)
(other settings are irrelvant for this example)
Error generated when submitting:
Unable to update the NT registry.
Cannot open HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources: Windows error number 5 occurred.Access is denied.
ColdFusion stores the data source, however a "verify" generates the following error:
Connection verification failed for data source: whatever
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Data source name not found and no default driver specified
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Data source name not found and no default driver specified

2. Creating the data source using the 32-bit version of "ODBC Data Source Manager" then trying to reference it with ColdFusion:
ODBC Data Source Manager:
System DSN or File DSN:
Name: whatever
Driver: Microsoft Access Driver (.mdb)
Database: (physical path to whatever.mdb)
(creation is successful within ODBC Data Source Manager)
Reference the above DSN within ColdFusion Administrator:
Data Source Name: whatever
Driver: ODBC Socket
Error generated when submitting:
Error accessing available odbc datasources. - Cannot open HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources: Windows error 2 occurred.The system cannot find the file specified.

ColdFusion MX 7 can't find the 32-bit data source on 64-bit Windows because it's looking for it in the wrong registry location.

Can ColdFusion MX 7 be configured to look in
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
instead of
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
on a 64-bit Windows machine?
TOPICS
Database access

Views

12.6K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 15, 2008 Dec 15, 2008

Copy link to clipboard

Copied

Joshhillman wrote:
"Can ColdFusion MX 7 be configured to look in
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
instead of
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
on a 64-bit Windows machine? "

I have a similar problem with ColdFusion 8.0.1 on Windows 2003 RC2 64-bit: CF is looking in the wrong registry key for DSNs (SQL and DB2) and so cannot find them. If you hear anything, please let me know.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 16, 2008 Dec 16, 2008

Copy link to clipboard

Copied

I haven't moved over any SQL Server-based ColdFusion sites yet, but I will be doing it soon. Since you mentioned a problem in your ColdFusion 8.0.1 on Windows Server 2003 RC2 x64, I decided to create a few tests in our ColdFusion 7.0.1 on the same operating system. I created three different "Microsoft SQL Server" data sources within CF 7.0.1 that successfully linked up to SQL Server 2005 Standard x64 Edition on the same machine as well as SQL Server 2000 and SQL Server 7 (both of which exist on two old Windows 2000 servers). All three data sources worked fine. None of these three successfully created SQL Server test data sources appear in the 32-bit or 64-bit versions of ODBC Data Source Administrator, so I guess they're only usable by ColdFusion. As a fourth test, I created a "SQL Native Client" data source within the 64-bit ODBC Data Source Administrator. In ColdFusion 7.0.2, I created a new test data source using the "ODBC Socket" instead of "Microsoft SQL Server" driver, but ColdFusion reported the following error:
Error accessing available odbc datasources. - Cannot open HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources: Windows error number 5 occurred.Access is denied.
The only sites we host that use data sources are ColdFusion based, so in our environment, it doesn't make much sense to create the data source within the ODBC Data Source Administrator. The many ASP and ASP.NET sites we host all connect directly to their databases (doesn't matter if it's SQL Server, Access, Excel, etc.) without ever needing to use ODBC DSNs.

What problems are you encountering with the SQL Server data sources you tried creating?

Although I've never dealt with DB2, I'm assuming the problem you're running into with it is the same that I am running into with a Microsoft Access based data source (and likely any other 32-bit ODBC driver) and ColdFusion not seeing where 32-bit drivers live.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 16, 2008 Dec 16, 2008

Copy link to clipboard

Copied

joshhillman wrote:
> 7.0.1 on the same operating system. I created three different "Microsoft SQL
> Server" data sources within CF 7.0.1 that successfully linked up to SQL Server
> 2005 Standard x64 Edition on the same machine as well as SQL Server 2000 and
> SQL Server 7 (both of which exist on two old Windows 2000 servers). All three
> data sources worked fine. None of these three successfully created SQL Server
> test data sources appear in the 32-bit or 64-bit versions of ODBC Data Source
> Administrator, so I guess they're only usable by ColdFusion. As a fourth test,

that's because those are all dsn based on datadirect's JDBC driver not ODBC.
given a choice you should *always* use cf's "native" JDBC drivers (those are
labeled w/the db name in cfadmin for all the big iron db, except of course for
access).


to the original problem, i can't remember the last time i used access but have
you given the "access for unicode" driver a try?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 16, 2008 Dec 16, 2008

Copy link to clipboard

Copied

Paul,

Thanks for the insight on this. Regarding the initial problem I ran into with creating Access based DSNs, very early-on, I noticed there were two different driver options for Access displayed within ColdFusion MX 7 Administrator:
Microsoft Access
Microsoft Access with Unicode

For reasons I'm am still baffled over, I somehow completely forgot about and through the wonders of apparent tunnel vision, completely overlooked the "Microsoft Access with Unicode" driver option when I was running through my tests.

At your seemingly-obvious suggestion, I created a data source using the Microsoft Access with Unicode driver and it worked perfectly fine with the one customer's web site I'm using for testing (of course, this is a copy--the production site is still housed on the old ColdFusion 5 server). This obviously doesn't actually fix the underlying issue with ColdFusion looking in the wrong part of the registry on 64-bit Windows, but at least when it comes to dealing with Access databases, your suggestion of using the Microsoft Access with Unicode driver appears to be a functional work-around. So long as there aren't any surprises waiting for me, hopefully this will also work for the remaining ColdFusion sites I have to migrate that are using Access databases.

Since you mentioned JDBC drivers in comparison with ODBC, I'm pointing out a couple of references in case they're helpful to anyone following this thread:

ColdFusion MX 7 - About JDBC
http://livedocs.adobe.com/coldfusion/7/htmldocs/00001736.htm

Types of JDBC technology drivers
http://java.sun.com/products/jdbc/driverdesc.html
http://java.sun.com/products/jdbc/

I don't actually develop anything in ColdFusion, so I'm dealing with all of this from a server administration standpoint since we do have some domain hosting customers that do develop or at least have existing ColdFusion based sites. I am all too-familiar with the caveats of using Access in any kind of production or heavily used environment and have spent years keeping users away from ODBC. For ColdFusion users, that meant creating OLE DB DSNs and often having to fix poorly written SQL statements and fixing ColdFusion date-related values and boolean values (the common problems encountered when switching CF / MS Access users from ODBC to OLE DB). I can only recall running into problems with a single SQL Server based ColdFusion site when migrating from ODBC to OLE DB. That customer never did get their many coding problems fixed, so they're the only ones still using ODBC in CF. I have no idea what'll happen when that one site gets migrated over--if it'll even work. Obviously a lot of things have changed between ColdFusion 5 and the MX versions that have come along afterward.

Thanks again for the Access with Unicode driver tip and JDBC info. It looks like I can finally start moving forward again with the site migration process.

Josh

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 17, 2008 Dec 17, 2008

Copy link to clipboard

Copied

just a reminder that you should test those cf5 sites w/that driver before you go
into production--if i recall there *are* some differences between those two
flavors of access.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 26, 2010 Apr 26, 2010

Copy link to clipboard

Copied

After hours and many blogs later, I've discovered the extremely easy solution to this error message.  I am on Windows Server 2008 / ColdFusion 8. Here's what I did:

1. Using the 32 Bit ODBC Administrator at C:\Windows\SysWow64\odbcad32.exe , created my ODBC datasource (in this case it was a .CSV text file)

2. Opened up REGEDIT and went to the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC node and right-clicked it and chose EXPORT, saving the file to my desktop as importme.reg.
3. Edited the .REG file in NOTEPAD and removed all references to WOW6432NODE, so that the path was now where ColdFusion was attempting to find the DSN in the first place:  HKEY_LOCAL_MACHINE\SOFTWARE\ODBC.

4. Save your changes to the .REG file, then double-click the .REG file to add it to your registry. By doing this, you create the non-existent node and ColdFusion can now see your 32 Bit ODBC DSN. Voila!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 02, 2010 Dec 02, 2010

Copy link to clipboard

Copied

I've been installing ColdFUsion 8 on Windows 2008 R2 x64.  I've had some success with a slightly different strategy, and arguably simpler answer:

When you install ColdFusion 8, make sure you right-click on the installer, and select "Run as Administrator".

You have to do this even if you're installing this as a domain administrator.  Although it won't give you an error message ever if you don't, running as administrator has made all my ODBC connections work properly.

Also, make sure you select "ColdFusion Documentation" during installation, as certain ODBC required files aren't installed unless you also install Documentation.

Frustrating, for sure.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 01, 2011 Jun 01, 2011

Copy link to clipboard

Copied

LATEST

As MX7 is a little dated now, I'll give my take on what seems to work with ColdFusion 8.

I've found that regardless of the environment (be it 2k8 server or earlier), all services and processes run by SYSTEM should have full access. As rmcqarry pointed out, you will need to run as administrator to grant this.

I'm not sure why the installation doesn't do some form of check to make sure that there are sufficient permissions for CF to run properly, but my guess is that in the Adobe development environment, the projects were created with administrator access by default.

I think there needs to be some update to the source code regarding permissions, and perhaps some way to validate installations to ensure all necessary files/processes etc are accessible with admin rights.

Regards,

Joseph Fox

Internet marketing & Designer

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines