11 Replies Latest reply on Oct 25, 2018 3:53 AM by sebd40107173

    CF16 and Access datasource

    plarts Level 1

      Since Coldfusion version 3, I work with Access datasources for information systems built with CF.

       

      Access datasource does still work with Coldfusion version 11.

       

      Then , with Coldfusion version 16,

      after days ans days of trials, CF16 cannot create Access datasource anymore !

       

      So , will next version of Coldfusion will be back with this historical functionnality ?

      All my work is based on this.

       

      Stays with CF11 for the moment.

       

      Thanks for any comment.

      I have never seen a new software version abandonning an historical and great functionnality.

       

      Pierre.

        • 1. Re: CF16 and Access datasource
          terribleted Level 1

          Plarts;   We both visited a couple of threads regarding CF16 this summer.  I was searching for an MS Access solution for new install of CF2016 Server and MS Server 2016.  The ODBC solutions suggested in dozens of posts from 2017 backwards did not work.

           

           

          I followed BKBK's suggestion to use HXTT  JDBC solution.    Re: accdb with CF9 under Win2008 R2 64bit

           

           

           

          My search brought me to HXTT's JDBC driver for MS Access.

           

          Repost of BKBK's 2012 solution:

           

          (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


           

          • 2. Re: CF16 and Access datasource
            BKBK Adobe Community Professional & MVP

            Hi @Plarts,

            Make a record of the installation and configuration steps that you followed. Also record the error messages. Then report a bug. I think that that is the fastest way to get back Access.

            • 3. Re: CF16 and Access datasource
              plarts Level 1

              Thanks for all your recommandations,

              But we spent too much time for this, days and days and days,

              we did not choose ColdFusion to loose so much time.

              Now my client is asking and waiting for a dregrade to CF11.

               

              I hope that Access datasource will be back in next version of ColdFusion.

              I work with this architeture since version 3 of CF with very great success.

               

              I do not understand why Adobe do not do the necessary to make Access datasource working with CF16.

              All screens in CFadmin are there to do it as usual, but not working.

              Too simple from Adobe to say :  it is not supported.

               

              If you know that this historical functionnality will be reactivated in next version of CF,

              thanks to tell me.

              That will be a technical direction decision to be taken for us.

               

              Pierre.

              • 4. Re: CF16 and Access datasource
                chrisa39177860 Level 1

                I followed your steps

                My search brought me to HXTT's JDBC driver for MS Access.

                 

                Repost of BKBK's 2012 solution:

                 

                (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

                 

                 

                and I got ok status

                 

                Capture.PNG

                 

                However, it only works partially. It is asking for $3xx to get a full version.

                • 5. Re: CF16 and Access datasource
                  BKBK Adobe Community Professional & MVP

                  That is quite an old solution. There is a more recent alternative, which is Open Source, hence free: UCanAccess.

                   

                  I did a test, and had it up and running on ColdFusion 2016 within 10 minutes. I followed these steps:

                   

                  1. From the UCanAccess website, download the current ZIP file, UCanAccess-4.0.3-bin.zip.

                  2. Unzip it.

                  3. Copy the following JAR files from the directories UCanAccess-4.0.3-bin and UCanAccess-4.0.3-bin/lib to the the lib directory of your ColdFusion installation (my ColdFusion lib directory is: C:\ColdFusion2016\cfusion\lib on Windows):

                   

                  ucanaccess-4.0.3.jar

                  commons-lang-2.6.jar

                  commons-logging-1.1.3.jar

                  hsqldb.jar

                  jackcess-2.1.9.jar

                   

                  4. Restart Coldfusion.

                   

                  That's it. The UCanAccess Java JDBC Driver for Microsoft Access is now installed.

                   

                  I then successfully ran the following test code:

                   

                  <cfscript> 

                  driver = createobject("java", "java.lang.Class").forName("net.ucanaccess.jdbc.UcanaccessDriver");

                  connection =createobject("java", "java.sql.DriverManager").getConnection("jdbc:ucanaccess://C:/Users/BKBK/Documents/new_us er_database/CompassTravel.mdb");

                  stmt = connection.createStatement();

                  resultSet  = stmt.executeQuery("select * from employees_with_department");

                  writeoutput("First Names:" & "<br>")

                  while (resultSet.next()) {

                       writeoutput(resultSet.getString("firstname") & "<br>");     

                  }

                  </cfscript>

                   

                  Notes:

                  My test Access database is located at C:/Users/BKBK/Documents/new_user_database/CompassTravel.mdb.

                  It has a table called employees_with_department.

                  The table has a column called firstname.

                  1 person found this helpful
                  • 6. Re: CF16 and Access datasource
                    mornings80 Level 1

                    Thank you for this information. How did you fill in this information:?

                     

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

                     

                    Data Source Name: myAccessDSN

                    Driver: ???

                     

                    JDBC URL: ????

                    Driver Class: ????

                    Driver Name: ????

                     

                    • 7. Re: CF16 and Access datasource
                      BKBK Adobe Community Professional & MVP

                      mornings80

                      Which Access driver have you installed?

                      • 8. Re: CF16 and Access datasource
                        mornings80 Level 1

                        Thank you for your reply. I was testing the UCanAccess driver you mentioned. I wasn't sure how to fill out the details when defining the datasource within Coldfusion Admin.

                         

                        Data Source Name: myAccessDSN

                        Driver: ?

                         

                        JDBC URL: ?

                        Driver Class: ?

                        Driver Name: ?

                        • 9. Re: CF16 and Access datasource
                          BKBK Adobe Community Professional & MVP

                          mornings80

                          if you follow the steps in my post of Feb 12, 2018 11:15 PM, then you will need to set nothing in the Administrator. The settings are in the code.

                           

                          In any case, as an alternative, you may configure the datasource in the Administrator as follows. Choose to "Add New Data Source" by entering, for example,

                           

                          Data Source Name: myAccessDS   

                          Driver: Other

                           

                          Then, click on myAccessDS to edit its properties, and add the following:

                           

                          JDBC URL: jdbc:ucanaccess://C:/Users/BKBK/Documents/new_user_database/CompassTravel.mdb

                          Driver Class: net.ucanaccess.jdbc.UcanaccessDriver

                          Driver Name: UcanaccessDriver

                          User Name: Admin

                          Password:

                           

                          Remember to start the User Name field with capital A and leave the password field blank.

                          That's it.

                           

                          My test code in this case was:

                           

                          <cfquery datasource="myAccessDS" name="q">

                          select *

                          from employees_with_department

                          </cfquery>

                          <cfdump var="#q#">

                          • 10. Re: CF16 and Access datasource
                            mornings80 Level 1

                            BKBK, thank you so much for your post. I was just one smashed keyboard away from giving up on this whole thing! Your information will be so valuable to others because I have seen so many posts where ppl can not get passed this CF / MS Access hurdle. Thank you very much!

                             

                            I am using Coldfusion Builder 16 trying to add an MSAccess (.mdb) data source. It was your UCanAccess post (above - 5. Re: CF16 and Access datasource) and your alternative (directly above - 9. Re: CF16 and Access datasource) using the "Add New Data Source" in CF that worked for me. Whew! Thanks.

                            • 11. Re: CF16 and Access datasource
                              sebd40107173 Level 1

                              I've compared HXTT and UCAN and HXTT is always much faster (10 times for simple query). Of course, it's not free. Also HXTT seems to trunc data to fit the field automatically and risk to corrupt the database.

                               

                              Both works with MS ACCESS 2017-2016 accdb format, ACCESS 2000 mdb and ACCESS 2003 mdb format

                              (no need to set the user name)

                              However only ACCESS 2000/2003 mdb format allows to set the password to protect and encrypt the database

                               

                              I had better performance with ACCESS 2003 format.

                               

                              It's the only way for me to connect an ms access database with recent CF (version 2016) and I don't understand why it's still possible to create a datasource by selecting MS Access in the CF admin. Last version where native CF MS Access driver works is CF 11 (according to me).

                               

                              Bottom line, I'll stay with CF11 and ODBC driver adding PWD variable in the registry (to protect the data).

                              If ODBC fails too much I'll move to UCAN or HXTT