1 2 Previous Next 56 Replies Latest reply: Jul 27, 2012 6:30 PM by drdagwood Go to original post RSS
      • 30. Re: accdb with CF9 under Win2008 R2 64bit
        BKBK MVP

        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?

        • 31. Re: accdb with CF9 under Win2008 R2 64bit
          Didi Community Member

          BKBK wrote:

           

          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?

           

          I guess I have done that before and it did not work - but I'll try it again (we NEVER give up ;-)

          • 32. Re: accdb with CF9 under Win2008 R2 64bit
            Didi Community Member

            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

            • 33. Re: accdb with CF9 under Win2008 R2 64bit
              BKBK MVP

              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.

              • 34. Re: accdb with CF9 under Win2008 R2 64bit
                Didi Community Member

                Sorry I did not mention that:

                 

                of course I always do the 'practical' test as you proposed.

                 

                It gives me an error with a coherent comment:

                 

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

                • 35. Re: accdb with CF9 under Win2008 R2 64bit
                  BKBK MVP

                  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

                  • 36. Re: accdb with CF9 under Win2008 R2 64bit
                    Didi Community Member

                    Hi BKBK

                     

                    Thanx! For the moment I am not able to try it on my system - but I will do ASAP :-)

                    • 37. Re: accdb with CF9 under Win2008 R2 64bit
                      Didi Community Member

                      Hi BKBK (as of now Sorcerer Of ColdFusion) 

                       

                      jdbc.GIF

                       

                      Does this take any more words ???  

                       

                      10^9 thanx to you!

                       

                      -Didi

                      • 38. Re: accdb with CF9 under Win2008 R2 64bit
                        BKBK MVP

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

                        • 39. Re: accdb with CF9 under Win2008 R2 64bit
                          Didi Community Member

                          hi there

                           

                          I would follow up on this thread since it perfectly documents the background.

                           

                          Let's summarize: with http://www.hxtt.com/access.html and BKBK's support we now have a working environment.

                           

                          One thing puzzles us:

                           

                          The following steps work fine:

                          1. SELECT qry from CF9 to the  DS
                          2. UPDATE DS from CF9
                          3. SELECT qry from CF9 to the DS (this reflects the change made in the step before)

                           

                          But this procedure does not work

                          1. SELECT qry from CF9 to the  DS
                          2. UPDATE the underlying table from within MS Access
                          3. SELECT qry from CF9 to the  DS (this does NOT reflect the change made in the step before)

                           

                          What we already tried:

                          • CFOBJECTCACHE does not help.
                          • Setting "" in CFadmin does not help.
                          • Disabling "Maintain Connections" in the Datasource does not help (see below).

                           

                          hxtt-1.GIF

                          Anybody a helpful idea?  (A recommendation to change the database is NOT regarded as helpful )

                           

                          -Didi

                          • 40. Re: accdb with CF9 under Win2008 R2 64bit
                            Owain North Community Member

                            A recommendation to change the database is NOT regarded as helpful

                             

                            Dammit, that's me out then.

                            • 41. Re: accdb with CF9 under Win2008 R2 64bit
                              BKBK MVP

                              Procedure 1:

                              1. SELECT qry from CF9 to the  DS
                              2. UPDATE something in DS from CF9
                              3. UPDATE something else in the underlying table from within MS Access
                              4. SELECT qry from CF9 to the  DS

                               

                              Procedure 2:

                              1. SELECT qry from CF9 to the  DS
                              2. UPDATE something in the underlying table from within MS Access
                              3. UPDATE something else in DS from CF9
                              4. SELECT qry from CF9 to the  DS

                               

                              Does these procedures work? That is, does step 4 reflect both changes made in steps 2 and 3?

                              • 42. Re: accdb with CF9 under Win2008 R2 64bit
                                HXTT Support

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

                                >But this procedure does not work

                                >    SELECT qry from CF9 to the  DS

                                >    UPDATE the underlying table from within MS Access

                                >    SELECT qry from CF9 to the  DS (this does NOT reflect the change made in the step before)

                                You need lockType=ACCESS connection property, then HXTT Access and MS Access can see each other.

                                For instance,  JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS

                                • 43. Re: accdb with CF9 under Win2008 R2 64bit
                                  BKBK MVP

                                  HXTT Support wrote:

                                   

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

                                  >But this procedure does not work

                                  >    SELECT qry from CF9 to the  DS

                                  >    UPDATE the underlying table from within MS Access

                                  >    SELECT qry from CF9 to the  DS (this does NOT reflect the change made in the step before)

                                  You need lockType=ACCESS connection property, then HXTT Access and MS Access can see each other.

                                  For instance,  JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS

                                  Wow! HXTT Support! What a privilege!

                                  • 44. Re: accdb with CF9 under Win2008 R2 64bit
                                    Didi Community Member

                                    BKBK:

                                    good idea to isolate the problem! -Thanx for the input!

                                     

                                    HXTT Support:

                                    Even better - now it works - triple Thanx for the solution!

                                     

                                    hxtt-2.GIF

                                     

                                    For those who recommend to change the DB (Owain, are you still there?   ):

                                    We made some performance tests and compared 'accdb plus HXTT' with SQL 2008 Express.

                                    'accdb plus HXTT' is incredibly fast. Regarding our application you cannot measure a difference in response time ..

                                     

                                    -Didi

                                    • 45. Re: accdb with CF9 under Win2008 R2 64bit
                                      Owain North Community Member

                                      When you have one user and all the files are already cached, maybe

                                       

                                      Wait for a second user to hit your site, it'll be like a DDOS.

                                      • 46. Re: accdb with CF9 under Win2008 R2 64bit
                                        Didi Community Member

                                        @ HXTT Support: are you still with us?

                                         

                                        As instructed by BKBK I installed the driver-library to

                                         

                                          "C:\ColdFusion\lib\Access_JDBC40.jar"

                                         

                                        However, when I replace Access_JDBC40.jar with the newer version Access_JDBC41.jar that was included with the package I received from you, it does not work.

                                         

                                        Do I need to configure a different Driver Class or JDBC URL ?

                                         

                                        Well, Access_JDBC40.jar works fine for me, but I guess, 41 has some advantages?

                                         

                                        -Didi

                                        • 47. Re: accdb with CF9 under Win2008 R2 64bit
                                          Didi Community Member

                                          Owain North wrote:

                                           

                                          When you have one user and all the files are already cached, maybe

                                           

                                          Wait for a second user to hit your site, it'll be like a DDOS.

                                          I absolutely agree, that accdb is not intended for a huge multiuser environment.

                                          However, ColdFusion is the only user.

                                          Since more than a decade I am told to trash MDBs since they will not perform.

                                          On the other hand I have applications with half a million hits per day that do not even twinkle between two requests. Otherwise students learning for exam would rub through my doormat

                                          As soon as performance drops, I definitely change ..

                                          Besides some legacy stuff I can't get rid off on the fast lane (we have applications running from the 90s),  I somehow have started to regard this discussion about the lousy performing mdb as a kind of intellectual challenge

                                           

                                          -Didi

                                          • 48. Re: accdb with CF9 under Win2008 R2 64bit
                                            HXTT Support Community Member

                                            >Well, Access_JDBC40.jar works fine for me, but I guess, 41 has some advantages?

                                            You cannot use Access_JDBC41.jar, since JDBC 41 API is for JDK1.7.X, and your ColdFusion is using JDK1.6.X. Both of Access_JDBC40.jar and Access_JDBC41.jar are released as v5.1 for different JDBC API standard.

                                            • 49. Re: accdb with CF9 under Win2008 R2 64bit
                                              HXTT Support Community Member

                                              >   When you have one user and all the files are already cached, maybe

                                              First, according to one customer's random access( which needn't  cached for recycle) report for  big file on CD-R, HXTT Access engine is faster than MS Access ODBC engine.

                                              Secondly, on lockType=ACCESS mode, HXTT Access won't use cache, since MS Access and HXTT Access can't share cache for concurrent according to old MS Access lock mechanism. HXTT Access has a faster data format parser/loader, and can utilize existent index fully for most sql.

                                              Thirdly, only without lockType=ACCESS mode, HXTT Access can use multi-level cache, for physical file, object query reslut, session, transaction. All core is smaller, and HXTT Access can run on mobile platform.

                                              Forth, only with assigned delayecClose connection property, HXTT Access can choose to build temporary index for big table without existent index.

                                              According to performance test, HXTT drivers is faster than most of popular databases.

                                              • 50. Re: accdb with CF9 under Win2008 R2 64bit
                                                Didi Community Member

                                                HXTT Support wrote:

                                                 

                                                You cannot use Access_JDBC41.jar, since JDBC 41 API is for JDK1.7.X, and your ColdFusion is using JDK1.6.X. Both of Access_JDBC40.jar and Access_JDBC41.jar are released as v5.1 for different JDBC API standard.

                                                Thanx!!!

                                                -Didi

                                                • 51. Re: accdb with CF9 under Win2008 R2 64bit
                                                  Didi Community Member

                                                  HXTT Support wrote:

                                                   

                                                  >   When you have one user and all the files are already cached, maybe

                                                  First, according to one customer's random access( which needn't  cached for recycle) report for  big file on CD-R, HXTT Access engine is faster than MS Access ODBC engine.

                                                  Secondly, on lockType=ACCESS mode, HXTT Access won't use cache, since MS Access and HXTT Access can't share cache for concurrent according to old MS Access lock mechanism. HXTT Access has a faster data format parser/loader, and can utilize existent index fully for most sql.

                                                  Thirdly, only without lockType=ACCESS mode, HXTT Access can use multi-level cache, for physical file, object query reslut, session, transaction. All core is smaller, and HXTT Access can run on mobile platform.

                                                  Forth, only with assigned delayecClose connection property, HXTT Access can choose to build temporary index for big table without existent index.

                                                  According to performance test, HXTT drivers is faster than most of popular databases.

                                                   

                                                  aha!

                                                   

                                                  So,

                                                   

                                                          jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS

                                                   

                                                  is ok for a developer environment.

                                                   

                                                  So, what configuration would you (HXTT Support, not you Owain ) recommend for a productive installation?

                                                   

                                                         jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb? ... delayedClose .. maxCacheSize .. lockTimeout.. etc. ???

                                                   

                                                  Is this for CF9 still valid as for CF6.1 (as stated in your FAQ)?:

                                                   

                                                         ?delayedClose=15;maxCacheSize=6144;lockTimeout=2000

                                                   

                                                  -Didi

                                                   

                                                   

                                                  PS: hey Owain, do not misunderstand me, it's not personal, just the challenge I mentioned above

                                                   

                                                   


                                                  • 52. Re: accdb with CF9 under Win2008 R2 64bit
                                                    HXTT Support Community Member

                                                    >So, what configuration would you (HXTT Support, not you Owain ) recommend for a productive installation?

                                                    It's only a jdbc url demo. You need only lockType=ACCESS since you need MS Access concurrent access. You can add other connection property if you need some special feature, for instance, ODBCTrimBehavior or caseInsensitive.

                                                    • 53. Re: accdb with CF9 under Win2008 R2 64bit
                                                      itisdesign MVP

                                                      Hi Didi,

                                                       

                                                      For MSAccess in 64-bit CF, you need the 32-bit drivers.

                                                       

                                                      Please follow these exact steps on the Win2008 R2 64bit CF9 machine:

                                                      1) Install the 32-bit AccessDatabaseEngine.exe.  Note: If 64-bit AccessDatabaseEngine_x64.exe or any 64-bit MS Office Apps are already installed, then AccessDatabaseEngine.exe must be installed via the command line with the "/passive" argument (ex: AccessDatabaseEngine.exe /passive).

                                                      2) Create DSN via CF Admin and ignore this error: "Unable to update the NT registry. Variable DRIVERPATH is undefined."

                                                      3) Register a "System DSN" using same name as in Step 2, via 32-bit C:\Windows\SysWOW64\odbcad32.exe, and choose this driver: "Microsoft Access Driver (*.mdb, *.accdb)"

                                                       

                                                      Thanks,

                                                      -Aaron

                                                      • 54. Re: accdb with CF9 under Win2008 R2 64bit
                                                        drdagwood Community Member

                                                        Aaron,

                                                            I followed your instructions above however I don't see the MDB db driver (at least that's what I named it when I created it in the SysWOW64)  so how do I make the "MDB" driver show in my CF Administrator DSN dropdown?

                                                         

                                                         

                                                        Connection verification failed for data source: MBD

                                                        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

                                                         

                                                        Thanks,

                                                        Daren

                                                        • 55. Re: accdb with CF9 under Win2008 R2 64bit
                                                          itisdesign MVP

                                                          Hi Daren,

                                                           

                                                          Step 2 was: 'Register a "System DSN"' (not "User DSN").  And that drop down on "CF Admin > Data sources" displays drivers, not data sources.  Here are the steps to correct the MSAccess/x64CF issue:

                                                           

                                                          1) Open C:\Windows\SysWOW64\odbcad32.exe

                                                          2) Click the "User DSN" tab

                                                          3) Select your data source (which you've named "MDB") and click "Remove"

                                                          4) Click the "System DSN" tab

                                                          5) Important: If your "MDB" data source exists here as well, then select it and click "Remove"

                                                          6) While still on the "System DSN" tab, add the "MDB" data source

                                                           

                                                          Basically, your data source must be deleted from SysWOW64's odbcad32.exe on both the User DSN tab and the System DSN tab.  THEN, once it doesn't exist on either tab, it can be added on the System DSN tab.

                                                           

                                                          Thanks,

                                                          -Aaron

                                                          • 56. Re: accdb with CF9 under Win2008 R2 64bit
                                                            drdagwood Community Member

                                                            Aaron,

                                                                I'm still having an issue...apparently I'm not smarter than the equipment I'm using. Here's what I did:

                                                             

                                                            1. I opened the C:\Windows\SysWOW64\odbcad32.exe and removed my old DSN under the "User DSN" tab
                                                            2. Opened hte "System DSN"
                                                            3. DSN = MBD
                                                            4. System Database I selected "Database“ and mapped it to my MBD.mdb database
                                                            5. Opened CF Administrator
                                                            6. Under "Ädd New Data Source" I typed MDB
                                                            7. Under "Driver" selected "MS Access"
                                                            8. Clicked "Add"
                                                            9. In "Database File" I mapped to my MBD.mdb (still receive error) and received the following error: (Connection verification failed for data source: MBD
                                                              java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.
                                                              The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.)

                                                             

                                                            9a. In "System Database File" I mapped to my MBD.mdb (still receive error) and received the same error.

                                                            When I go to my CF pages that pull data from the database I receive the following error: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.

                                                             

                                                            Any idea what I’m doing wrong?

                                                            1 2 Previous Next