Skip navigation
Currently Being Moderated

accdb with CF9 under Win2008 R2 64bit

Jan 27, 2012 8:50 AM

Hi there

 

I have a accdb designed with Access 2010 64bit.

I am running CF9 under Win2008 R2 64bit.

I have also installed http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13 255

 

I have now googled and tried some dozen tricks to get this combination work:

 

until now - no way!

 

Does anybody succeed to to so and is there somewhere a description?

 

Thanx for help - Didi

 
Replies 1 2 Previous Next
  • Currently Being Moderated
    Jan 30, 2012 5:05 AM   in reply to Didi

    1) Did you in every case add the following line to the Connection String field in the ColdFusion Administrator?

     

    Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_mdb/accdb_file

     

    2) Do you have any 32Bit MS Office applications installed on the server?

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 30, 2012 11:21 AM   in reply to Didi

    Didi wrote:

     

    ad 1)  I try again

    I guess it must be in the form c:\mypath\myDB.accdb

    Backslashes, not forward slashes?

     

    Your path OK.

     

    When I add a new Datasource in CFadmin, what Type of Driver do I select??

    MS Access

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 31, 2012 1:24 AM   in reply to Didi

    Ah, I see. That is a well-known problem. Had you registered the system DSN yet?

     

    In any case, let's start all over again. Delete everything from the Connection String field. The steps follow.

     

    1) You'll have to start by registering the (MS Access) System DSN. To do so, double-click on the file c:\windows\SysWOW64\odbcad32.exe to launch it.

     

    2) Select the tab System DSN and press Add. Select Microsoft Access Driver(*.mdb, *.accdb) and press Finish.

     

    3) Give the datasource a name. (Jot it down, as it is the same datasource name you will have to use in the ColdFusion Administrator.) Now, double-click on the datasource  name (Alternatively,  press Configure.). You should now get an interface with which to add a database.

     

    4) Press Select. Under the Directories: field, locate the directory in which you have stored your MDB or ACCDB file. The list of files in the directory should automatically appear in the field to the left.

     

    5) Click on the database you require, and press OK. In so doing, you have coupled the database to the datasource.

     

    6) Press OK, and again OK, to leave the ODBC interface.

     

    7) Verify that Windows has created the right registry values. To do so, run Wndows' regedit on the command-prompt. You should have registry entries similar to the following:

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\myAccessDSN\

        DBQ=C:\Users\BKBK\Documents\myDB.accdb

        Driver=C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE14\ACEODBC.DLL

        DriverId=dword:00000019

        FIL=MS Access

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources\

        myAccessDSN=Microsoft Access Driver (*.mdb, *.accdb)

     

    While configuring Windows ODBC, the name I gave for my datasource was myAccessDSN. That is also the name I am giving to the datasource in the Administrator. The location of my Access database is C:\Users\BKBK\Documents\myDB.accdb. That was it then.

     

    After following the above procedure, I still got the following familiar error message when I attempted to verify the datasource in the Administrator:

     

    Unable to update the NT registry.

    Variable DRIVERPATH is undefined.

     

    That is known to possibly be a bug. So I ignored it. I went ahead and ran a cfquery on the Access database. It worked!

     

    Useful references:

     

     


     
    |
    Mark as:
  • Currently Being Moderated
    Feb 1, 2012 12:26 AM   in reply to Didi

    There is no Microsoft Access Driver(*.mdb, *.accdb)

     

    You don't need to look any further. It is clear from your picture that you've only got the older versions of the drivers. Like you, I have versions 6.01.7600.17632. But I have also got versions 14.00.4760.1000, too.

     

    Something must have gone wrong during your installation of the MS Access Database Engine 2010. Reinstall it.

     

    If you encounter any problems installing the 64-bit version, then install the 32-bit version. Restart Windows(!) and follow the 7 steps I gave above.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 2, 2012 3:42 AM   in reply to Didi

    From what you've said, we don't need to check. Your installation went smoothly. So, if you installed 64-bit, then you have 64-bit.

     

    However, I am now of the opinion that, what you need in order to run Access, are the 32-bit drivers! Yes, the 32-bit, not the 64-bit, drivers! I would even wonder aloud: 64-bit MS Access 2010 may sometimes require only 32-bit drivers, for example, on an Operating System running certain Microsoft 32-bit applications like Office(and certain applications like ColdFusion). Notice how Microsoft themselves put the 32-bit binary odbcad32.exe in the 64-bit directory c:\windows\SysWOW64\. This means it also applies to 64-bit!

     

    Of course, the gist of this all is that 32-bit software may run on a 64-bit Operating System like your Win2008. So, the Microsoft Access Driver (*.mdb, *.accdb) on my 64-bit Windows 7 must have come from my 32-bit installation of the MS Access Database Engine 2010.

     

    A test worth doing is to uninstall the 64-bit Access 2010 engine, and install the 32-bit version. You cannot have both versions on the same machine.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 2, 2012 11:37 AM   in reply to Didi

    Here's the catch. Windows 64bit is capable of running 32bit software. You know this if you think about it. In fact the odbcad32.exe file above is a 32 bit control panel applet for managing 64 bit drivers. So the trick is to figure out how to manage 32bit drivers on your system. The good news is that the old version of the ODBC manager (the one that handles 32 bit drivers) still exists on your 64bit machine. Take a look in the %systemroot%/sysWOW64/ folder. You will see a version of the same file - odbcad32.exe. Open this control panel applet and you will discover that it actually manages the old list of 32 bit drivers

    Indeed. That was also what I observed.

     

    I know it's convoluted - a file in "system32" called odbcad32.exe manages 64 bit drivers while a file in the sysWOW64 folder manages 32 bit drivers. If you need to know, the "sysWOW64" folder stands for "Windows on Windows 64" - meaning plain old Windows 32 bit aps running on the Windows 64 bit platform.

    I don't find it convoluted at all. I, too, came across the meaning of WOW64. In fact, I am surprised that, having discovered what it stands for, you still went ahead and used the binary odbcad32.exe in System32 rather than the version in SysWOW64! That is why you got this error: "The specified DSN contains an architecture mismatch between the Driver and Application".

     

    In any case, that error message also tells me that your ACCDB file may be 64-bit. Again, for the purposes of creating Microsoft Access DSN in ColdFusion, you will need to create a 32-bit System DSN using this 32-bit application: c:\windows\SysWOW64\odbacd32.exe.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 3, 2012 1:16 AM   in reply to Didi

    12 and 14 are the names that Microsoft's developers gave under the hood for, respectively, Office 2007 and Office 2010. So, where you see version 12.0, Microsoft Office 2007 Access is being implied. Where you see version 14.0, Microsoft Office 2010 Access is being implied. You appear to have both Access versions installed. Could it be that you are mixing up the version 12.0 driver with the version 14.0 Access?

     

    Besides Office version, the other main consideration is Bitness. On 64 bit Windows, such as yours, 64 bit applications are stored in Program Files (Progra~1, for short), and 32 bit applications are stored in Program Files (x86) (Progra~2, for short). Furthermore, the Windows system directory for 32-bit files is C:\Windows\SysWOW64, and the Windows system directory for 64 bit files is C:\Windows\System32.

     

    64 bit Windows can handle 32 bit as well as 64 bit versions of an application. However, it's all down to how you put your configuration together.

     

    The driver for Access has the same name in Office 2007(v. 12.0) and in Office 2010 (v. 14.0), which is, ACEODBC.DLL. The location of that driver will be as follows, depending on whether the Office version is 32 bit or 64 bit:

     

    32 bit Office 2007: C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE12\ACEODBC.DLL

    64 bit Office 2007: C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE12\ACEODBC.DLL

    32 bit Office 2010: C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE14\ACEODBC.DLL

    64 bit Office 2010: C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE14\ACEODBC.DLL

     

    I'll illustrate using my own case. I have 32-bit Microsoft Office 10. The Access driver my system uses is therefore C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE14\ACEODBC.DLL. Scroll up to the 7 steps I gave above. You will see that that is indeed the value of the driver in my Windows registry. You, too, should verify your registry entries.

     

    How did I configure the Access System DSN? By following the 7 steps above. My Office version is 32-bit, so I used C:\Windows\SysWOW64\odbcad.exe. If your Office version is 64 bit, then you should use C:\Windows\System32\odbcad.exe instead.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 3, 2012 4:25 AM   in reply to Didi

    Didi wrote:

     

    ok, on my server, no MSO is installed at all. The my.accdb is created on a Win7 64 bit  with MSO 2010 64bit - no doubt about that!

    Then I copied it to the server (Win2008 R2 64bit)

    I would assume that Windows installs Microsoft Access 2010 as a Microsoft Office 2010 application. OK, yours is 64 bit. Did you install the 64 bit AccessDatabaseEngine_x64.exe on Win2008? My guess is that you should

     

    some puzzles remain ..

     

    Since my MSO is 64 I tried now (having installed AccessDatabaseEngine.exe ) C:\Windows\System32\odbcad.exe

    A mistake. AccessDatabaseEngine.exe is 32 bit, and so you require C:\Windows\SysWOW64\odbcad.exe for it.

     

    I have a 64-bit Office with which I create a my.accdb, then I copy it to a 64bit server.

    Why do I need to install AccessDatabaseEngine.exe and not AccessDatabaseEngine_x64.exe ?

    You don't need to. To keep everything to 64 bit, you should install AccessDatabaseEngine_x64.exe! However, as I said before, it's all about 2 things: driver version and bitness. If you wish to run  applications that require 32-bit Access drivers, you should install AccessDatabaseEngine.exe instead.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 3, 2012 5:08 AM   in reply to Didi

    Are you sure you *really* want to go through all this just to use Access...?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 3, 2012 7:20 AM   in reply to Didi

    Didi wrote:

     

    Well, lucky boy who has a snapshot :-)

     

    Now I am back at the point where I entered this thread, but now with a more precise description of what I am doing:

     

    1. I have a 64-bit Office on a Win 7 64bit Workstation, with which I create a my.accdb.
    2. Then I copy it to a server running CF9 under Win2008 R2 64bit..
    3. I have installed AccessDatabaseEngine_x64.exe
    4. I use  C:\Windows\System32\odbcad.exe
    5. Creating a DSN it offers me v14.0 drivers
    6. Adding a new Datasource in CFadmin says:

     

    movo-odbc64-mismatch.GIF

     

    But at least we seem now to agree, that both of us assume that I am doing the right thing? Don't we?

    Or do I still miss something?

    I missed 2 things. You mentioned nothing about uninstalling the 32 bit engine nor about the registry.

     

    Uninstall AccessDatabaseEngine.exe. Restart Win2008. (Re)Install AccessDatabaseEngine_x64.exe.

     

    Corresponding to the above, the 7 steps for 64 bit Microsoft Access 2010 are:

     

    1) You'll have to start by registering the (MS Access) System DSN. To do so, double-click on the file c:\windows\System32\odbcad32.exe to launch it.

     

    2) Select the tab System DSN and press Add. Select Microsoft Access Driver(*.mdb, *.accdb) and press Finish.

     

    3) Give the datasource a name. (Jot it down, as it is the same datasource name you will have to use in the ColdFusion Administrator.) Now, double-click on the datasource  name (Alternatively,  press Configure.). You should now get an interface with which to add a database.

     

    4) Press Select. Under the Directories: field, locate the directory in which you have stored your MDB or ACCDB file. The list of files in the directory should automatically appear in the field to the left.

     

    5) Click on the MDB or ACCDB database you require, and press OK. In so doing, you have coupled the database to the datasource.

     

    6) Press OK, and again OK, to leave the ODBC interface.

     

    7) Verify that Windows has created the right registry values. To do so, run Wndows' regedit on the command-prompt. You should have registry entries similar to the following:

     

    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\myAccessDSN\

     

        DBQ=C:\Users\BKBK\Documents\myDB.accdb

        Driver=C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE14\ACEODBC.DLL

        DriverId=dword:00000019

        FIL=MS Access

     

    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\

     

        myAccessDSN=Microsoft Access Driver (*.mdb, *.accdb)

     

    In this configuration, myAccessDSN is the name I give for my datasource. You should see your own instead. MyAccessDSN is also the name I would give to the datasource in the Administrator. The location of my Access database is C:\Users\BKBK\Documents\myDB.accdb. That was it then.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 4, 2012 3:26 AM   in reply to Didi

    You and I have gone as far as we can. My conclusion is that, when you install an MS Access datasource in the ColdFusion Administrator, ColdFusion uses a 32-bit application, macromedia.jdbc.MacromediaDriver, in the process.

     

    To see this, run the following code (using your own admin password):

     

    <cfset adminLogin=createobject("component","CFIDE.adminapi.administrator").l ogin("my_cf_admin_pw")>

    <cfset ds=createobject("component","CFIDE.adminapi.datasource")>

    <cfdump var="#ds.setMSAccess#" >

     

    This shows that the driver class is macromedia.jdbc.MacromediaDriver. In fact, the following code will let you see even further.

     

    <cfset macromediaDriver=createobject("java","macromedia.jdbc.MacromediaDrive r")>

    <cfdump var="#macromediaDriver#" >

     

    Macromedia.jdbc.MacromediaDriver is actually a legacy engine for setting the appropriate driver for a collection of databases, namely for DB2, Informix, MySQL, and so on. One of them, SequeLink, is for MS Access. I do believe that this construction is 32-bit, hence the incompatibility.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 8, 2012 4:26 AM   in reply to Didi

    That would in fact be my suggestion now: download and install an appropriate legacy 64 bit JDBC-ODBC driver.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 8, 2012 4:36 AM   in reply to BKBK

    BKBK wrote:

     

    That would in fact be my suggestion now: download and install an appropriate legacy 64 bit JDBC-ODBC driver SQL Server Express.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 8, 2012 5:01 AM   in reply to Owain North

    Owain North wrote:

     

    BKBK wrote:

     

    That would in fact be my suggestion now: download and install an appropriate legacy 64 bit JDBC-ODBC driver SQL Server Express.

    Point taken. However, mine is not to wonder why Access.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 8, 2012 6:28 AM   in reply to Didi

    Completely agree it's not always so easy, and I'm sure no developer would ever be the one to choose Access. Take this thread as an example though - how many hours have you burned trying to get it to work? It's Access 2010, so someone somewhere is still actively upgrading your Access database, presumably still thinking it's the right thing to do.

     

    I appreciate it's not always easy, but Access is not made for web applications. It never will be, and every time you come to change/upgrade you'll go through this much pain and then some.

     

    If you can, I'd look at moving away from it before you find support is pulled completely

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 8, 2012 12:21 PM   in reply to Didi

    Didi,

    Before I go JDBC, just one last stab with 64 bit, for the purpose of elimination. Set the DSN up in the Administrator, choosing "Microsoft Access" as driver.

     

    For the "Database File", browse to and select your (64 bit) ACCDB file. Next, click on "Show Advanced Settings". Enter the following as "Connection String":

     

    jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=absolute_path_to_accdb_file

     

    Click to "Submit" the changes. What is the result?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 9, 2012 12:34 AM   in reply to Didi

    Didi wrote:

     

    I tried

     

    • jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=Z:\DATABASE\Didi\2010-64.accdb    OR
    • jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=Z:\DATABASE\Didi\2010-64.accdb;   OR
    • jdbc:odbc:Driver=Microsoft Access Driver (*.mdb,*.accdb);DBQ=Z:\DATABASE\Didi\2010-64.accdb

     

    as well as

    • Database File = Z:\DATABASE\Didi\2010-64.accdb   AND/OR
    • System Database File = Z:\DATABASE\Didi\2010-64.accdb

     

    Same result:

     

    When registering:

     

    Unable to update the NT registry.

    Variable DRIVERPATH is undefined.

     

    When verifying:

     

    Connection verification failed for data source: movo2accdb

    java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application

    The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application

    Sufficient:

     

    • jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=Z:\DATABASE\Didi\2010-64.accdb
    • Database File = Z:\DATABASE\Didi\2010-64.accdb

     

    Register. No need to verify. Now, run a ColdFusion CFM page containing the following code:

     

    <cfquery name="testQ" datasource="movo2accdb">

    select *

    from your_table_name

    </cfquery>

     

    <cfdump var="#testQ#">

     

    If this test succeeds, then your 64-bit installation will have worked. The error would have been caused by ColdFusion engaging the (my guess) 32-bit Macromedia driver and by the NT/DRIVERPATH issue which many consider a longstanding bug.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 13, 2012 1:38 AM   in reply to Didi

    OK. That was that then. Let's move on.

     

    I do believe our hopes now lie with JDBC. Since ColdFusion is a Java application, finding a 32-bit and 64-bit JDBC driver for MS Access will help many developers.

     

    My search brought me to HXTT's JDBC driver for MS Access. The only problem is, I couldn't tell whether their driver is for 32-bit or 64-bit Access. Could it (hopefully) be universal, automatically detecting the system? 

     

    (After the customary virus scan) I unpacked their access.zip file, copied the essential file Access_JDBC40.JAR from /access/lib/ to ColdFusion's lib directory. That's all there is to installing the driver. I then restarted ColdFusion.

     

    I went to the ColdFusion Administrator and configured a datasource as follows:

     

    Data Source Name: myAccessDSN

    Driver: Other

     

    JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb

    Driver Class: com.hxtt.sql.access.AccessDriver

    Driver Name: com.hxtt.sql.access.AccessDriver

     

    It worked flawlessly. But then again, though I installed everything you did, I am on 32-bit Access. 

     

     

    accessDSN.jpg

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 19, 2012 9:25 AM   in reply to Didi

    Thanks to you, too, Didi, for offering me this opportunity to learn.

     
    |
    Mark as:
1 2 Previous Next
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points