ColdFusion-10 64bit and MSAccess

July 23, 2012 / Milan Chandna 19 Comments

 

Have you ever faced a problem in trying this combination - ColdFusion10 64bit with MSAccess (.mdb or .accdb).

Many have faced this problem and the sole reason is because ColdFusion doesn't support MSAccess in 64bit. Additionally, Windows doesn't currently ship with a MSAccess .accdb driver.

Though there is no support officially, there is a workaround and the complete credit for finding and testing this goes to Aaron Neff. I am just blogging here on his part.

 

Here are a few steps to follow to make this combination work for you.

1. (This first step is only required for .accdb files.) Install 32-bit AccessDatabaseEngine.exe<http://www.microsoft.com/download/en/details.aspx?id=13255>

1a. Important: If AccessDatabaseEngine_x64.exe or any 64-bit MS Office applications are already installed, then AccessDatabaseEngine.exe must be installed via the command line with the "/passive" argument. (ex: AccessDatabaseEngine.exe /passive)<http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable>

2. CF Admin > Data Sources > New DSN

3. Enter a Data Source Name (ex: MyAccessDSN). Select "Microsoft Access" from driver list. Click "Add".

4. Click "Browse Server" next to "Database File" and browse to your .mdb (or .accdb) file. Click "Submit".

5. Ignore the red text at top which says "Unable to update the NT registry. Variable DRIVERPATH is undefined."

6. Run C:\Windows\SYSWOW64\odbcad32.exe. Be sure to select the "System DSN" tab. Click "Add".

7. Choose this driver if it exists: "Microsoft Access Driver (*.mdb, *.accdb)". Otherwise, choose "Microsoft Access Driver (*.mdb)". Click "Finish".

8. Use same Data Source Name as in Step 3.

9. Click "Select" and browse to same .mdb (or .accdb) as in Step 4. Click "OK". Click "OK" again.

10. Run <cfquery datasource="MyAccessDSN" and wahlah!

 

Here are the details of the issue.

In 64-bit ColdFusion, the ODBC Agent and Server (SequeLink) processes run as 32-bit:

- swagent.exe *32

- swsoc.exe *32

- swstrtr.exe *32

 

However, clicking "Add" in Step 3 registers the Access DSN as 64-bit by creating these registry entries: 

- key: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\{Data Source Name here}

- value: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\{Data Source Name here}

 

Instead, clicking "Add" in Step 3 should register the Access DSN as 32-bit by creating these registry entries (Steps 6-9 are the workaround for this):

- key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\{Data Source Name here}

- value: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources\{Data Source Name here}

 

To illustrate, just perform these 4 additional steps:

11. Delete the 64-bit DSN entries from the registry (after creating a registry backup):

    - key: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\{Data Source Name here}

    - value: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\{Data Source Name here}

12. Run <cfquery datasource="MyAccessDSN" and wahlah!

13. CF Admin > Data Sources

14. Click the Verify checkmark icon for the same data source. See Status is "OK"!

 

Thanks again to Aaron.

 

Milan.

 


19 comments so far ↓

  • 1 Joseph // Jul 24, 2012 at 6:42 AM
    While it's nice to know how to get around this on my local dev server I don't think this will be feasible for me to do on my live hosting server cause it is a shared hosting server. Don't get me wrong I am happy to see this and I've bookmarked it for future reference but when I first encountered this I just took it to mean I needed to learn a better dbase solution anyway.

    Thanks for the update but now I've learned of a better way of doing things to get around this problem.
  • 2 Jerry Holte // Nov 25, 2012 at 2:25 PM
    When will Cold Fusion 10 support current 64 bit MS Access without me having to manually fix it to work around the Adobe short comings? When I first learned of this issue I decided to stop my four upgrade purchases (two Dream Weaver and two Cold Fusion). Then I changed my mind. My support of Adobe products Is waning. Obviously my needs are not important!
  • 3 G // Nov 29, 2012 at 9:18 AM
    Hey thanks a bunch! I was trying to connect adobe cold fusion 10 to ms access 32 bit and it was not working. I was able to get mine working without step 1.

    Thanks
  • 4 Aaron Neff // Jan 25, 2013 at 5:09 PM
    Hi Milan,

    Just a follow-up note for others:

    If user permits settings migration during CF10 installer (i.e. CF9 settings are migrated into CF10), then CF10's neo-datasource.xml will have port 20000 (instead of 20002) for each ODBC data source.

    Fix is: Open CF10's neo-datasource.xml and replace all 20000 w/ 20002.

    It might be helpful if Adobe can post a CF10 version of the following article: http://blogs.adobe.com/vikaschandran/2010/09/10/odbc-datasources-migrated-to-cf9-from-cf8-fail-with-error-on-verification/

    Thanks!,
    -Aaron
  • 5 Aaron Neff // Jan 25, 2013 at 5:14 PM
    Note: That Adobe CF article says "Restart the Cold fusion". That should be "ColdFusion"! :/
  • 6 Aaron Neff // Jan 29, 2013 at 10:00 PM
    I've created the following tickets for CF10 ODBC issues:

    3492574 - CF installer migrates ODBC data sources w/ wrong port
    3492580 - ODBC DSN creation fails in non-cfusion instances
    3492608 - ODBC DSN deletion fails in non-cfusion instances
    3492620 - 64-bit CF10 creates wrong registry entries for ODBC data sources

    3492620 is for the issue discussed in this blog post.

    3492574 and 3492580 are for other issues and contain temporary workarounds for those issues.

    If, after following the steps in the post above, anyone is still having difficulties with ODBC on CF10, then please also refer to these ticket numbers for possible workarounds.

    Thanks!,
    -Aaron
  • 7 Yongxin Zhao // Feb 2, 2013 at 2:16 AM
    >I don't think this will be feasible for me to do on my live
    > hosting server cause it is a shared hosting server.
    If you haven't right to setup ODBC service, the commerical solution is use pure Java HXTT Access driver for 64bit. It's at http://www.hxtt.com/access.html .
  • 8 Scott Busche // Feb 27, 2013 at 11:17 AM
    Thanks for this! We have a few access databases that we need as readonly and while the first setup correctly as an ODBC connection, the other had to be setup as Access with the ODBC connection, odd.
  • 9 John S. // Oct 15, 2013 at 8:17 AM
    I'm working on a project and needed to set up a local version using an mdb database. There's another complex workaround for this issue but yours is simple, elegant, and it works.
    Thank you
  • 10 HUGO LEONARDO - BRASIL // Nov 18, 2013 at 3:07 PM
    Thank you, worked perfectly, once again thank you!
  • 11 Tom // Jan 9, 2014 at 1:01 PM
    There was a unicode access driver before, but now it's gone. Any ideas how to workaround this Would be apprechiated!
  • 12 Lukester85 // Jan 23, 2014 at 5:59 AM
    Why use Access databases at all? The better fix for a production server is not to use them, upgrade to MySQL or MSSQL. As far as I am aware Adobe did not include support for Access because it is not supported on a server, it is a desktop database solution.
  • 13 John // Mar 18, 2014 at 5:16 PM
    STOP USING MICROSOFT ACCESS! Use MSSQL or MySQL.
  • 14 Michael Markowski // Apr 2, 2014 at 1:21 PM
    Or use Apache Derby embedded which is built in to ColdFusion 9/10.
  • 15 Tom // Apr 10, 2014 at 4:48 AM
    Get a reality check! Some of us have to work with legacy systems and do not always have the option to choose what to use as database.
  • 16 John Pullam // Apr 24, 2014 at 8:03 AM
    Higher up in this thread a fellow mentioned the HXTT driver as a viable alternative for 64 bit Access. We purchased it for a CF9 environment and it has worked perfectly ever since. Plus, the support was good when we had questions. So I consider it a viable alternative ... but I'm looking for a native solution from Adobe in the future. Would love to get out of Access but I didn't write the application.
  • 17 Lieven // Apr 28, 2014 at 7:24 AM
    This workaround did fine, up until now. I changed nothing, except that I think the operating system did an upgrade, and as a result the SYSWOW64 directory disappeared, and my datasource suddently isn't working anymore. So I get the error:

    Connection verification failed for data source:
    com.inzoom.jdbcado.JdbcAdoException: Must specify data source property in connection string
    The root cause was that: com.inzoom.jdbcado.JdbcAdoException: Must specify data source property in connection string
  • 18 Carl Von Stetten // Apr 29, 2014 at 1:24 PM
    @Lieven,

    If your \Windows\SYSWOW64 directory truly "disappeared", then your operating system is corrupted. All 64-bit Windows OS versions (at least through 2008R2) should have the SYSWOW64 folder. Sounds like your problem is not ColdFusion-related but OS-related.
  • 19 Hugo Leonardo // Jun 3, 2014 at 10:32 AM
    Thank you, problem solved.

Leave a Comment

Leave this field empty:

Blue Mango Theme Design By Mark Aplet

Super Powered by Mango Blog