Cold fusion 2021 - when i try to add new datasource through administrator page, i'm getting below me

[tnsenthil]

Hello,

when i try to add new datasource through administrator page, i'm getting below error message.

NOTE:

Im using cold fusion 2021 version with coldfusion builder 2018

I tried to add microsoft access driver. Could you please let me know how to fix this issue?

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

[Dave Watts]

This might be a dumb question, but have you installed the ODBC Services as part of your CF install? The Microsoft Access driver that is included from DataDirect (I think that's the vendor) requires them.

Dave Watts, Eidolon LLC

[tnsenthil]

Thanks Dave.

I have installed ODBC Servicees as part of MyCF Install. but for some reason the services are not running. even if i start manualy .

[Charlie Arehart · Accepted solution]

What Dave says may indeed get you going, but I won't be surprised if either you don't find the services, or you can't install them.

First, some may notice when installing CF2021 that when you get to the page about installing optional services (like the .NET service, the Solr service, the PDFG service, and indeed the ODBC service), the CF2021 installer curiously grays out the ODBC one, so you can't turn them off or on. But after the install (via the full CF installer) you WILL find the "ColdFusion 2021 ODBC Agent" and Server services are installed--but you can't start them. Nor will they start from the command line.

And I have not found any log in either the slserver54 folder (within CF, used by those services) nor in the CF logs folder that explains what's amiss. Perhaps someone else may have more luck digging for that.

Some more confusion could arise from tnsenthil indicating that they are using CFBuilder. Note that when you install CFBuilder, it also offers an option to install CF (which gets implemented in a CF folder WITHIN CFBuilder rather than the traditional CF folder). And I am pretty sure that installer does NOT even offer to install the external services, like ODBC.

That's why I say that you may not even find the ODBC services (and while you can find SOME such additional installers for CF at the downloads page, the ODBC service has never been offered there. Instead there are blog posts from Adobe over the years showing how to add the ODBC service via some CFML code calling the Admin API.)

Is all this ugly as hell? Yes. Frustrating for those wanting to use ODBC? Yes. And it's clear that you want to use Access in particular--and by default it relies on ODBC. But as Dave and I have discussed with folks in other forum threads in recent weeks, ODBC's time has come and gone (really, before the turn of the century). Starting with CF6 (in 2002), all CF datasources OTHER than Access started using JDBC drivers instead.

And while Adobe provides JDBC drivers for most DBs, they never did for Access. Are there JDBC drivers for Access? Yes there are, and you should really consider that instead. See the discussion in the other post from last month, where BKBK shared news of that and a post he's done in the past on it. He even offered an updated list of steps in a later post in that thread. (I don't know that Adobe will ever bundle that with CF. Really, Access should not be used with a multiuser application server like CF. Folks should be moving to anything else.) But for those who want it, the option is there.

Let us know if any of this gets you going.

[tnsenthil]

Thanks a lot Charlie for the detailed explanation.

You are correct, As you said "the CF2021 installer curiously grays out the ODBC one, so you can't turn them off or on. But after the install (via the full CF installer) you WILL find the "ColdFusion 2021 ODBC Agent" and Server services are installed--but you can't start them. Nor will they start from the command line."

Also My coldfusion 2021 Adminstrator page behave differently on each time when i loged in.(for exaple data sources link tab under DATA and SERVICES link is not selectable ).

So, I uninstall both cf builder and coldfusion server and again re installed only coldfusion server 2021.

This time i have noticed the ODBC Services are running properly and adminstrator pages also working fine without cold fusion builder( not sure about this). 

so again i tried to create MS Acesses DSN, this time DSN created with connection verification Error,

"Connection verification failed for data source: ASDSN
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined."

Not sure what mistake i have made. Im still working on this. Thanks once again.

[Charlie Arehart]

That is indeed all interesting to hear. And to me the most important is to hear that somehow there IS a JDBC driver underlying the builtin cf support for access. I guess I never noticed that. 

Anyway, since it's not working (perhaps due to a remaining issue with that odbc service), you could and should still consider that alternative jdbc driver. It would NOT have anything to do with the odbc service.

And you've not responded to the assertion that perhaps it's time to get off Access. It's a sisyphus-like situation. 

Any thoughts in either of those? 

[Dave Watts]

I kind of think you understand this already but are just missing something in the description. So for the benefit of everyone else: there's an underlying JDBC driver for everything in CF, because that's literally all that CF understands. That's what the SequeLink stuff is - it's a JDBC-ODBC bridge provided by DataDirect and bundled with CF. It consists of the ODBC Agent and ODBC Server services. For the MS Access option in CF to work, CF has to tell SequeLink to create an ODBC data source in Windows, then use that ODBC data source with the CF JDBC stuff.

My suspicion here is that the SequeLink software isn't able to create a 32-bit ODBC data source, and that's what Access needs or needed. Again, I've avoided working with Access long enough that I don't remember the details any more.

And that of course is my recommended course of action even if you could get Access to work here. Access is not designed to work well in this role, and never has been. Using Access is a great example of going to the doctor and saying "it hurts when I do this", and the doctor responding "don't do this!"

Dave Watts, Eidolon LLC

[tnsenthil]

True.

[tnsenthil]

It is true, MS Access time is over and I too not using MS Access for the last 15 years.

Actually, I started my carrier with Allaire’s ColdFusion version with MS Access in 1999 till 2003 after that now only I started re learning ColdFusion 2021 just on my personal interest.

I just tried to re build my old ColdFusion projects with CF2021 as part of learning process otherwise not very particular about MS access.

But after seeing all the community discussion, I thought of moving to alternate suggested by you. Thanks again for your suggestion.

[Charlie Arehart]

I'm glad to see you've come around to letting go of Access, and that you regard the question as resolved.

But I am surprised that you marked your last comment as the answer. For the question you raised, you had acknowledged above that my first one answered that. And I did also there  (like Dave later) recommend you move off Access.

I don't press this point to "win" but sincerely to help someone else who may later find your question to see the more specific answer (and suggestion). Or do you disagree? 

[tnsenthil]

Yes, I Agree with you.

[Charlie Arehart]

OK, thanks. Somehow you had not only your previous comment but then my last one marked as the answer, as WELL as my first one. 🙂 I'm surprised the system allows that. But it was then showing at the top only the LAST one as "the answer" (in green). In order to make the first answer stand out, I have removed the marking of your and my later comments as being "answers". Hope you don't mind.

And while I have that power (as a moderator of the forum), I very rarely use it. I much prefer folks make choices themselves. I only did this because it seemed from your last comment that it's what you would want done.

Again, glad your sorted, and hope the disucssion may help others going forward.

[enricomangia]

Hello,

I'm very surprised about these notes...

I run an unstaller GUI that have an option to install an ODBC driver, but is grey...but to have it installed you have to remove the installation (even if grey and unchecked, the installer install it...) and then reinstall it...it doesn't sound like an installer of an Enterprise Application that cost thousand of dollars, don't you think so?

But the point remain the same: I have a server with ColdFusion 2018 Enterprise working fine with ODBC datasource to connect to databases on a IBM Power8. Then I purchased 2021 version, I installed it and ODBC gives me error (and all the configuration of datasources, ODBC drivers, etc has remained exactly the same.

Nobody in the community has a solution for it?

And please, not saying "put IBM Power 8 in the trash", because for sure I will put 2021 update in the trash...

Thank in advance for any support

Enrico

[Charlie Arehart]

Enrico, what is the db you are connecting to? You don't say (only that it's running on IBM Power 8). If it's db2, there is indeed  jdbc driver for that included in cf enterprise (2021 or 2018 or earlier), as well as the free trial and developer editions. Have you ever tried those? I realize you didn't need to before, because odbc worked. 

And even the Standard edition supports you getting and using ANY jdbc driver, and there are available ones for db2.

Let us know if that may get you going. I really think it's past time to struggle to get odbc to work in 2021. I appreciate that you may disagree vehemently and want you want. You should consider opening a ticket at tracker.adobe.com to see if Adobe engineers may offer a better answer. They rarely interact here. 

[BKBK · Accepted solution]

@tnsenthil and @enricomangia, the community's advice that you phase out Microsoft Access from your applications is good advice. MS Access has had its time.

However, you may be stuck with MS Access for a while yet. For example, you may not have the resources or the time to migrate to a new database. But you have to keep delivering services. So what then?

Nil desperandum! I have good news for you: Ucanaccess.

With Ucanaccess, you will still be able to create MS Access datasources in ColdFusion 2021. I explained how you can do so, step-by-step, in a previous post: https://community.adobe.com/t5/coldfusion/coldfusion-2021-and-access-database/m-p/11735544  

[BKBK]

Oh, Ucanaccess is Open Source. If Ucanaccess helps you, will you please consider making a donation.

[BKBK]

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:

[minals74665441]

what you did for  JDBC error.how it solved i am facing same problem.what to do now?

[Charlie Arehart]

There's only one jdbc error in this thread, where tnsenthil reported  getting, "[Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined."

Are you getting EXACTLY that, about sequelink? And are you trying to use an odbc datasource? If so, this entire thread offers diagnostics and alternatives for that. If you're NOT using odbc, you really should start a new thread. 

Otherwise, if using odbc please tell us first what kind of database are you connecting to? Then, what have you tried in the way of the diagnostics and alternatives suggested here? 

[minals74665441]

i am using cold fusion2021,for ms-access data source.error is attached.initally i am using coldfusion buider2018  that time i get error as 

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..so i unistall coldfusion builder2018 and install coldfusion2021 developer edition now i am able to see screen of datasoucre for ms-acess but now it giving me error as jdbc error.plese help

[Charlie Arehart]

Did you try yet Mike's suggestion below, from last year?

If that did not help, tell us what cf2021 update you're on (see the cf admin settings summary page). And you're confirming now that the two cf2021 odbc services are running?

Finally, have you at least tried the ucanaccess option that has been offered here and elsewhere pointed to here? Even if it's not your

preference, at least consider it as a sanity check and backup alternative if nothing else can be made to work. 

[TheRealMC]

Hi all,

Apologies for ressurecting an old thread, but I'd just like to add a comment to describe the method by which I managed to get the ODBC Client/Server services running correctly, so it may provide assistance to others in the future.

1. In the CF 2021 Administrator, go to 'Package Manager'
2. In Package Manager, expand the 'Installed Packages' section
3. Select the 'ODBC' package and click 'Uninstall'
4. In Package Manager, expand the 'Available Packages' section
5. Select the 'ODBC' package and click 'Install'.

I should add that I haven't used MS Access in many, many years, and I certainly don't advocate using ODBC when a JDBC option is available. The only reason I had to get those services working was to connect to an old legacy system where ODBC was the only option.

Hope this helps someone in the future!

Cheers,

Mike.

[minals74665441]

It work thanks