CF16 and Access datasource

Report · Sep 12, 2017

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.

Report · Oct 02, 2017

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

Report · Jan 18, 2018

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

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

Report · Feb 12, 2018

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:

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_user_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.

Report · Jul 10, 2018

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: ????

Report · Jul 10, 2018

mornings80

Which Access driver have you installed?

Report · Jul 10, 2018

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: ?

Report · Jul 11, 2018

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:

select *

from employees_with_department

</cfquery>

Report · Jul 11, 2018

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.

Report · Oct 25, 2018

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

Report · Feb 20, 2020

Thank you so much for this! I have spend the last five days pounding my head against my desk trying to get CF 2018 to connect to my Access DB!

Report · Mar 20, 2020

I know this is an old thread but I got UCanAccess to work, at least to talk to my database.
This issue I am seeing is that a CF Insert returns an error message.

In troubleshooting, I reduced the number of items to insert down to one, just to see if I had a field type mismatch, but still errors out.

Error Executing Database Query.

UCAExc:::5.0.0-SNAPSHOT data exception: invalid character value for cast

The error occurred in C:/inetpub/wwwroot/employee/emp_add_action.cfm: line 3

1 : <cfquery name="insertrecord" datasource="employees">
2 : INSERT INTO emp (NewHire)
3 : VALUES ('#form.NewHire#')
4 : 		</cfquery>

It seems a CF Update works just fine with UCanAccess.

If anyone has any insight, I would greatly appreciate it. I have been struggling for far to long with getting CF 2018 working with Access.

Thanks

Report · Oct 02, 2017

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.

Report · Oct 08, 2017

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.