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.

 

32 Comments to “ColdFusion-10 64bit and MSAccess ”

  1. Joseph
    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
    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
    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
    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
    Note: That Adobe CF article says "Restart the Cold fusion". That should be "ColdFusion"! :/
  6. Aaron Neff
    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
    >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
    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.
    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
    Thank you, worked perfectly, once again thank you!
  11. Tom
    There was a unicode access driver before, but now it's gone. Any ideas how to workaround this Would be apprechiated!
  12. Lukester85
    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
    STOP USING MICROSOFT ACCESS! Use MSSQL or MySQL.
  14. Michael Markowski
    Or use Apache Derby embedded which is built in to ColdFusion 9/10.
  15. Tom
    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
    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
    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
    @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
    Thank you, problem solved.
  20. eric
    No luck. Install runs without any reported error, but still unable to create an Access DSN. (CF10 on Windows Enterprise Server.) Fails with the error "The ColdFusion ODBC Server service is not running or has not been installed. You may also use the "MS Access with Unicode" driver to connect to MS Access datasources."

    So, not possible to proceed beyond step 3.
  21. Kory
    Thank you for the work around! This fix worked in a ColdFusion 11 and Office 2013 environment.
  22. Tom E Kola
    Did not work with ColdFusion 2016, Windows 7. (connect adobe coldfusion 2016 to Access 2010 .mdb 32 bit )
  23. Charlie Arehart
    Tom (#22), how about clarifying what specifically "did not work"? Maybe someone will see a solution for you.

    Also, if there is no solution for you, it doesn't mean that it won't work for "anyone" in CF 2016 on Win7.

    Finally, if it can't work for you, did you consider the HXTT option mentioned above (#7)? It's worked for others here.
  24. Pavithra
    It is not working in ColdFusion16.
  25. Charlie Arehart
    @Pavithra, care to elaborate? Any error messages? I've not heard others say it didn't work, except Tom above, who when I asked him to tell us more, did not reply. Can you see my reply before yours (from May 6) and let us know if any of that helps, and if not, let us know more.
  26. Aaron Neff
    Hi all,

    Yes, the steps have changed in CF2016:

    1. (same step as in original post above)
    2. Run C:\Windows\System32\odbcad32.exe. Be sure to select the "System DSN" tab. Click "Add".
    3. Choose this driver if it exists: "Microsoft Access Driver (*.mdb, *.accdb)". Otherwise, choose "Microsoft Access Driver (*.mdb)". Click "Finish".
    4. Enter a Data Source Name (ex: MyAccessDSN).
    5. Click "Select" and browse to your .mdb (or .accdb) file. Click "OK". Click "OK" again.
    6. CF Admin > Data Sources > New DSN
    7. Use same Data Source Name as in Step 4. Select "Microsoft Access" from driver list. Click "Add".
    8. Click "Browse Server" next to "Database File" and browse to your .mdb (or .accdb) file. Click "Submit".
    9. Ignore the red text at top which says "Unable to update the NT registry. Variable DRIVERPATH is undefined."
    10. Run <cfquery datasource="MyAccessDSN" and wahlah!

    In CF2016, the ODBC Agent and Server (SequeLink) processes run as 64-bit on x64 Windows (instead of always as 32-bit in older CF versions), good. However, CF Admin's Data Sources page doesn't add all the necessary registry entries (will log bug). That is why NOW the DSN needs to be created via odbcad32.exe FIRST (and it needs to be the file in the system32 directory, not SYSWOW64), and -then- added via CF Admin.

    Thanks!,
    -Aaron
  27. Charlie Arehart
    Great Stuff, Aaron. Thanks, on behalf of those needing this solution.

    I hope Adobe may just create a new blog post with this info, as some folks just don't seem to take the time to read through all comments on a blog post (like these) when scrambling for a solution.

    (Of course, people really ought to seek to get off Access. There are so many other better free database servers, but that too is fodder for another post.)
  28. Aaron Neff
    Hi Charlie,

    Just saw your message, sorry for the delay. Thanks very much! Yes, I agree w/ you regarding Adobe creating a new blog article and agree ppl won't always read all the comments here.

    I always use Access. JK!! XD Actually, I -only- use it in 1 specific case: In an intranet, where I'm interacting w/ a Windows desktop program that stores its data in .mdb. So, I do find it handy, but of course wouldn't use it in production.

    ..and I gotta remember to file that bug about the invalid registry entries.

    Thanks!,
    -Aaron
  29. FRANK MARESCO
    Aaron,

    Thanks for the tip about CF 2016 and going to the system32 folder .... on my windows server 2012 Access is not listed as an option but it is on the other odbc's in the syswow64 .... how do I add the drivers to the system dsn? thxs
  30. Aaron Neff
    Hi Frank,

    You're very welcome! I mistakenly said "1. (same step as in original post above)". But that isn't correct. For CF2016, Step 1 should be:

    -----------
    1. Install AccessDatabaseEngine_X64.exe<http://www.microsoft.com/download/en/details.aspx?id=13255>;

    1a. Important: If AccessDatabaseEngine_x32.exe or any 32-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>;

    Thanks!,
    -Aaron
  31. FRANK MARESCO
    AAron,

    Thank you for the tip again ... unfortunately I cannot get the 32 bit or the 64 bit cold fusion 2016 to connect to the access db ....
  32. FRANK MARESCO
    SORRY ... Disregard ... I did get the 32 bit version working and unfortunately I am up against a hard deadline and have to save the 64 bit version for another day .... either way I think the fix for my 32 bit would have worked with the 64 bit once I got the drivers working ... my problem had to do with the naming convention of my ODBC connectors to access ..... thxs

Leave a Comment

Leave this field empty: