ColdFusion 2021 and Access database

New Here ,
Jan 07, 2021 Jan 07, 2021

Copy link to clipboard

Copied

I've just installed ColdFusion 2021 as a Development server.  I'm trying to attach an Access database as a Datasource.  It comes back with something like "ODBC service not running, or is not installed."  When I was installing CF2021, the ODBC Services checkbox on the sub-components installation page is greyed-out, so I am unable to check it.  What next?

 

TOPICS
Database access

Views

490

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 08, 2021 Jan 08, 2021

Copy link to clipboard

Copied

Moriarty, the first thing that needs to be said ("what next?") is that in the year 2021, it really is time to get off of Access as a db used for any web app server like cf (and yes, even if used by only one person, let alone many).

 

I know that will upset some. And others may disagree, perhaps vehemently. Let me elaborate. 

 

First, yes, CF has long supported it, but it no longer does formally, at least according to the cf2021 support matrix:

 

https://helpx.adobe.com/pdf/coldfusion2021-support-matrix.pdf

 

And this is not new in cf2021. The last support matrix to list it was in fact cf11, from 2014.

 

But yes, the cf admin still lists it as a db type, as you are seeing. (I almost wonder if that's a mistake, though you will even find some comments from Adobe folks since 2014 trying to help with workarounds.) 

 

And yes, there is indeed still the cf odbc service. (As you say it's greyed out in the 2021 gui installer. That said, I find it's still being enabled as a service on windows, regardless, which was a surprise.) BTW, that's not there JUST for Access but for any use of odbc--which really also ought to be strongly discouraged these days.

 

And use of odbc itself is hardly needed any more: to be clear, all other db's that cf supports (and offers drivers for) are instead JDBC drivers. These have been the main db types since cf6 in 2001. But there are some very old integrations that still support only odbc--and even those are a challenge to keep working in recent cf releases. 

 

Indeed, people have been trying to "keep access to Access" in CF in recent releases. You will find on the web (and may hear here) various discussions of workarounds trying to get it/keep it going. 

 

All are like bailing water on a sinking ship. Get out of the boat. Switch to any alternative that cf DOES support. And note that there are free implementations of sql server, mysql, oracle, postgres, and more. And yes, I mean free for production use

 

Yes, converting the db can be (or seem like it would be) a hassle, but there are tools to help, especially between Access and sql server. And no, there may be zero need of any code or sql changes, so the conversion effort really could take just minutes or hours, though of course testing is needed. Such a conversion should not be a crazy long effort, though one would understandably fear that. 

 

(And yes, this move would disrupt people who have long done things like upload a new mdb to the server from their own machine or a local network. Again, that was never a great idea even 20 years ago, though people had their justifications. There are ways to do the same upload/sync process with a db in sql server, etc. They're just different steps. And they're nearly always better in many ways.)

 

I know all this is not what people using Access want to hear, especially if this is in the critical path of moving to a new cf version. They may even demand of Adobe that this "must be made to work". 

 

And again some may toss you supplies to keep the boat afloat. I can no longer in good conscience try to help people plug those holes. 

 

The boat is sinking. Really, get off of it. But I'm referring only to use of Access with CF, of course. The *cf boat" is awesome, upright, and especially in cf2021 fitted with modern features and conveniences to carry its passengers and crew forward, despite the sneers from folks in other boats, or even those wanting to debark cf at their next chance. 

 


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 08, 2021 Jan 08, 2021

Copy link to clipboard

Copied

Moriarty,

My experience is probably similar to yours. I made, and still make, Access databases for all sorts of things. And I used it for my public web sites for many years, as well. A while back, I started having issues with the server from time to time. My host supported me as best they could, but they finally told me that I was never going to have reliable use of an Access database with my site. 

They suggested MySQL, and I went with it. After I went through the trouble of figuring out the connections & how to use the provided app, phpMyAdmin, I came to love it. You can create ODBC connections between your local site and your DB on the remote server, and it will work swimmingly.

 

Good luck!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 09, 2021 Jan 09, 2021

Copy link to clipboard

Copied

ODBC

It might be worthwhile to install the ODBC service. If only because you might need it for something else. But beware of YAGNI, a condition that afflicts each and every one of us.

To install ODBC, proceed as follows:

  1. Open the ColdFusion Administrator; click on the last but one item in the menu panel on the left-hand side.
  2. You should see ODBC among the "Available Packages". If you do, click on it, then click on "Install", and you're done.
  3. If ODBC appears instead among the "Installed packages", then you should first uninstall it. To do so, click on it, then on Uninstall. Now, reinstall ODBC as in step 2.

 

Microsoft Access 

As Charlie rightly says, you shouldn't consider using MS Access on CF2021. Especially not with ODBC.

 

However if, for whatever reason, you insisted on using MS Access on CF2021, then I would suggest you do so with JDBC instead of ODBC. You could then use the Open Source JDBC driver, UCanAccess; I did a post on it some years ago.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 09, 2021 Jan 09, 2021

Copy link to clipboard

Copied

I see there have been one or two updates to the UCanAccess JDBC library. So, for your convenience, here are the steps for using the UcanAccess JDBC driver to run MS Access on ColdFusion 2021:

 

  1. Go to the UCanAccess web site and click on the download link. 
  2. Download the file UCanAccess-5.0.1.bin.zip and unpack it.
  3. Copy the following Jar files to your \ColdFusion2021\cfusion\lib directory:
     UCanAccess-5.0.1.bin\UCanAccess-5.0.1.bin\ucanaccess-5.0.1.jar
     UCanAccess-5.0.1.bin\UCanAccess-5.0.1.bin\lib\commons-lang3-3.8.1.jar
     UCanAccess-5.0.1.bin\UCanAccess-5.0.1.bin\lib\commons-logging-1.2.jar
     UCanAccess-5.0.1.bin\UCanAccess-5.0.1.bin\lib\hsqldb-2.5.0.jar
     UCanAccess-5.0.1.bin\UCanAccess-5.0.1.bin\lib\jackcess-3.0.1.jar
     Restart ColdFusion 2021
  4. In the ColdFusion Administrator, create a new datasource having driver-type "Other". Then fill in the details as I have done in the following example (the path is the location of my Access DB file; you may choose your own Driver Name; you should use the default username 'admin', even if your Access DB requires no authentication; you may leave the Password field empty)
        CF Data Source Name: myAccessDB
        JDBC URL: jdbc:ucanaccess://C:/Users/BKBK/Documents/myTestAccessDB.accdb;memory=false
        Driver Class: net.ucanaccess.jdbc.UcanaccessDriver
        Driver Name: ucanaccess
        User name: admin
        Password:

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 09, 2021 Jan 09, 2021

Copy link to clipboard

Copied

LATEST

Great point, BKBK, about the options of available JDBC drivers. I keep forgetting to think of those (for those who desparately feel they need to stick with Access), but I should have recomended that or the other (that I seem to recall having seen).

 

Glad you offered that...along the the reminder to seriously reconsider getting off Access. The  JDBC driver certainly helps resolve the ODBC issues, but it doesn't really address how Access is just not really designed for the volume of multi-user access that a web app/web server can throw it--though again I realize some will say "it was never a problem for all our years of using it".

 

In such a case, the JDBC driver can serve as the oars to keep the rickety boat moving. 🙂


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines