12 Replies Latest reply on Jan 5, 2018 12:20 PM by Charlie Arehart

    create data source for microsoft access on 64 bit server and ColdFusion 16

    HCR

      Trying to migrate our Microsoft Access DB's to a new server running Microsoft Server 2012 and ColdFusion 16. I have been able to do this in the past using the 32 bit ODBC Data Source Administrator, but it doesn't seem to be working this time.

        • 1. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
          haxtbh Level 4

          Access is no longer on the support matrix for Coldfusion so it maybe that it is no longer supported in 2016.

          1 person found this helpful
          • 2. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
            Vikram_k1810 Adobe Employee

            Could you please let us know the error message you are getting while mapping the MS Access data source name?

             

            Thanks,

            Vikram

            • 3. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
              HCR Level 1

              I received the following instructions from support and this has fixed my problem. Thanks.

               

              Thank you for contacting Adobe ColdFusion support.

               

              Please try the below instructions and let me know if it works:

               

              Step 1: Uninstall any existing “Microsoft Access database engine 2010” from add\remove program.

               

              Step2: Ensure that you don’t have any MS Access related data source defined in ColdFusion admin page(under Data Source).

               

              Step 3: Ensure that you have removed all the DSN entries from odbc administrator(both 32 bit and 64 bit under system DSN).

               

              64 bit odbc path: C:\Windows\System32\odbcad32.exe

              32 bit odbc path: C:\Windows\SysWOW64\odbcad32.exe

               

              Step 4: Download and install 64 bit MS Access Database Engine from the link: http://www.microsoft.com/download/en/details.aspx?id=13255

               

              Step 5: Once the installation is completed, you will have to define the DSN using 64 bit drivers.

               

              Use 64 bit odbc path: C:\Windows\System32\odbcad32.exe

               

              Step 6:  Go to ColdFusion Admin page and map that DSN using MS Access drivers and verify.

              • 4. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                elliottr33939969

                I keep getting 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"

                I have tried the above solution but the error persists.

                • 5. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                  plarts Level 1

                  Hello HCR,

                  I am in the same situation,

                  with CF16, I cannot declare the Access datasource used before Under CF11.

                   

                  So, I followed step by step the support instructions, (installing AccessDatabaseEngine_X64.exe),

                  I am on a Windows 10 64bits, CF16 64bits,

                   

                  And the result is the same : failed.

                   

                  Could Adobe support advise, what to do ?

                  Any suggestion, help.

                   

                  for the moment, CF16 is unusable.

                  Thansk a lot

                  • 6. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                    halbob

                    Plarts,

                     

                    I also installed CF16 over Server 2016 this past few days.  I have tried the suggested workaround for the earlier editions mentioned in original posting.   I actually tried about 5 different configurations from other postings found on Google.  None worked.

                     

                    When I try to submit the data connector, the area is greyed out and no successful submission.  The database connector does show a database entry after cancelling the process. but when tested the standard error message pops up.

                     

                    In closing, I downloaded SQL Express which is free and have begun the migration process.  I spent way too many hours with the 6 possible solutions found.

                    • 7. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                      plarts Level 1

                      I wait a CF11 key from Adobe.

                      I do not understand what happens between CF11 and CF16 for this feature less.

                       

                      I cannot migrate to another database,

                      I use 5 different Access databases in the application.

                      Very flexible to copy and replace when necessary in the application.

                      And it will take too much time to convert to another database and review the full architecture of the system functions.

                      6 months of work.

                       

                      Also in the CF16 product page, it is said that it works with Access database.

                      https://helpx.adobe.com/coldfusion/configuring-administering/administering-coldfusion.htmlpage_cf_admin.jpg

                       

                      Thanks for any help and suggestion.

                      Pierre.

                      • 8. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                        halbob Level 1

                        HCR, quick question, which series of Access drivers did you install, 2010 or 2016?  Of those, did you use 32 or 64 bit?

                         

                        I ask as I tried default no installed drivers using SYSWOW6432 and then iterations of 2010 and 2016 drivers.  I could not initiate a handshake with the drivers while performing an online test of our CF2016 trial and MS 2016 Server using IIS 10. 

                         

                        I was able to get the HXTT drivers installed successfully. In a separate post, I described lingering issues with connectivity.

                         

                        HXTT driver setup (HXTT MS Access JDBC Drivers )  Click on "Download directly JDBC 3.0 trial". It is a zip file

                         

                        Download the zip file, extract  Access_JDBC30.jar

                        Drop the file in [cf_root]lib file.

                        Restart CF Server

                         

                        Open CF Admin and go to DataSource. 

                        CF Data Source Name (your data name)

                        JDBC URL: jdbc:access:/(your_data_drive,eg:C:)/your_data_file.mdb

                        Driver Class com.hxtt.sql.access.AccessDriver

                        Driver Name com.hxtt.sql.access.AccessDriver

                         

                        Click Submit

                        • 9. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                          mcollins323 Level 1

                          Just an FYI

                           

                          I got it to work with CF2016

                           

                          I just made sure all my odbc definitions were removed.

                          Installed the 64 bit version of the Access Engine

                          Defined my odbc in the odbcad32 as describe above

                          Then went to CFadmin and defined it

                          CF reports

                          • data source updated successfully.

                          I then ran this test sql and it worked just fined.

                            <cfquery name="check" datasource="testaccess">

                              SELECT now() AS dt           

                          </cfquery>

                          <cfdump var="#check#">

                           

                          No need to event restart CF even.

                           

                          HTH

                          1 person found this helpful
                          • 10. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                            plarts Level 1

                            Hi, thanks for info,

                             

                            I tried this, with no success.

                             

                            - I removed all ODBC declaration (32 and 64 bits)

                            - I dropped the Access_JDBC30.jar file in : c:/coldfusion16/cfusion/lib

                            - I declared the CF datasource as follow :

                                      - name : test_16

                                      - Driver : other

                             

                                      Then :

                                      - datasource name : test_16

                                      - JDBC URL : jdbc:access:/C:/ColdFusion2016/cfusion/wwwroot/test/site_user.mdb

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

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

                             

                            When accessing the datasource with a CFM query

                            And I get the error :

                            Error Executing Database Query :

                            No suitable driver available for test_16, please check the driver setting in resources file,

                            error: com.hxtt.sql.access.AccessDriver 

                             

                            In CFAdmin, when submitting the datasurce, I get the yellow error message :

                            Connection verification failed for data source: test_16

                            java.sql.SQLException: No suitable driver available for test_16, please check the driver setting in resources file, error: com.hxtt.sql.access.AccessDriver

                            The root cause was that: java.sql.SQLException: No suitable driver available for test_16, please check the driver setting in resources file, error: com.hxtt.sql.access.AccessDriver

                             

                            Could you tell me where could be the problem in my datasource declaration ?

                             

                            Is the driver setting : other or access driver ?

                             

                            Thanks for help.

                            Pierre.

                            • 11. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                              mcollins323 Level 1

                              All,

                               

                              To follow up:

                               

                              I did not remove anything when I did my setup.  I was prepared to if I got the error but wanted to just try without removing anything.

                               

                              Also - when I tried setting up on a more secure 2012 server running 2016 Updater3, it did not work, Cfadmin would not connect and the sql would not run.  So we went to 2016 Updater5 restarted CF APP service and ODBC Services and it worked!  CFadmin was able to connect and the test sql worked fine. 

                               

                              HTH

                              • 12. Re: create data source for microsoft access on 64 bit server and ColdFusion 16
                                Charlie Arehart Adobe Community Professional & MVP

                                Pierre, I know your last reply here was from a few months ago, but since I know some people who are paying attention to this thread, I wanted to follow-up with you. You said you had gotten the jar, put it in the lib, created an "other" DSN, and got the error about "No suitable driver".

                                 

                                What you don't indicate is whether you had restarted CF, as halbob indicated was the final step. Usually, the "no suitable driver" is because of that (CF does not have the jar loaded). Is it possible you did since then do that restart, and all did work and you just forgot to tell us? Or did you maybe give up and never try to verify the DSN again after a restart? Please do let us know.

                                 

                                I will add that resorting to a 3rd party jar (like that hxtt one) should not be necessary, for those wanting to create and use an Access DSN (though that should be discouraged generally) on 64-bit CF10 or 11. The steps above about creating a DSN in the Windows 32-bit ODBC control panel (and one in the CF Admin of the same name) should suffice. (More in a moment on CF2016, where that alone does not seem to suffice.)

                                 

                                Note that one DOES need to have the CF ODBC Windows service implemented and running (and it would not be, if whoever ran the CF installer on a gien machine told it not to bother. I think that's what caused the error Elliot reported before your post, "ColdFusion ODBC Server service is not running". Fixing that involves simply calling an Admin API method to create those services, as discussed Re: ColdFusion 11 ODBC service.)

                                 

                                Back to CF2016, these steps alone don't seem enough. As HCR and mcollins323 discussed in other comments here, they found they needed to install also the 64 bit MS Access Database Engine, also. Pierre, I know you said that had not worked for you. But rather than delve into that, and since you later brought up this challenge using the hxtt driver, I thought we ought first make sure a simple restart of CF might not have solved that problem (as it should have).

                                 

                                Bottom line, though folks really ought not be using MS Access still in 2018, those who choose to or feel forced should still be able to get it working one of these various ways.

                                 

                                One last thing: Pierre noted in an earlier comment that the CF Admin doc page for the CF Admin still referred to Access, though note that was in passing regarding the ODBC Server service. It seems worth noting that Access is NO LONGER listed in the CF 2016 support matrix" document listing supported systems and databases: https://helpx.adobe.com/pdf/coldfusion2016-support-matrix.pdf. It WAS indeed listed in the CF11 one: https://wwwimages2.adobe.com/content/dam/acom/en/products/coldfusion/pdfs/cf11/coldfusion1 1-support-matrix.pdf.

                                 

                                So it seems we should likely not expect much further help from Adobe to get Access DSNs working again on CF2016 or beyond. We may even find that the Access option in the CF Admin DSN driver type list could someday be removed. In that case, the "other" option and using the hxtt driver would seem the only recourse for those who choose not to move off of Access.

                                 

                                But no discussion of enabling use of Access should be left without an acknowledgment that for nearly 20 years people have been told it is NOT a DBMS suited to multi-threaded web applications. Does it work for some? Sure. Might it fail them in ways they've never realized, or that better alternatives would not? Sure. As Halbob also indicated, one can consider the free SQL Server Express edition (which really is free for production, with merely a 10g limit per database, and for which MS offers migration tools to move to from Access). Or there are of course MySQL and other DBMSs that can be used for free. See my cf411 list of DB alternatives, at http://www.cf411.com/db.