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.

[haxtbh]

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

[Vikram Kumar M]

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

Thanks,

Vikram

[HCR]

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.

[plarts]

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

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

[plarts]

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

Thanks for any help and suggestion.

Pierre.

[halbob]

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

[mmarkows]

Thanks for posting this set of instructions, they worked fantastic for me.  I also got the "connection verification failed" error in CF Administrator when I first tried to set up an Access datasource (.mdb file) with CF 2018 Developers Edition.  Then I deleted all of the DSNs from my laptop, and installed the 64 bit MS Access Database Engine as instructed.  Didn't find any previous version of the Access Database Engine installed, so I then used the 64 bit version of odbcad32.exe to define the DSN using 64 bit drivers.  Then I created the DSN in CF2018 Administrator and voila, it immediately started working.  I'm posting this to inform folks that this 6-step procedure posted by HCR works just as well for CF2018 !  Many thanks. - Mike  

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

[Mike Collins_CF]

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

[plarts]

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.

[Charlie Arehart]

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/coldfusion11-support-....

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.

[Mike Collins_CF]

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

[jpullam]

I just followed these instructions for a CF 2016 installation and they worked fine. (At least the data sources said they were good.)

I'm not using Access out of choice. I have 2 legacy databases that I convert to SQL but still need to check them for updates each day.

[Charlie Arehart]

jpullam, are you referring to the instructions above shared by HCR in the 3rd reply here, or perhaps one of the follow-ups? Just trying to help others who may find this in the future and get their hopes up.

Along the same lines, can you clarify what update level of CF2016 you were on? and what Windows version? and is CF or Windows 32-bit?

For those who don't know, one can run CF2016 and earlier as 32-bit on a 64-bit Windows. Again, CF2018 no longer supports 32-bit CF--and to be clear, the sys requirements for CF2016 and above no longer list Access as supported.

Indeed, before anyone castigates me for trying to "encourage" folks here seeking to use Access, see my extended comment above from Jan 2018. That's NOT what I'm doing. I just try to help people solve problems, wherever they are, sharing suggestions and even recommendations as I go.

[jpullam]

Yes. I followed the instructions shared by HCR in the 3rd reply. I am installing CF2016 developer edition at update 11 on a new Windows 10 system, all running 64 bit.

[Charlie Arehart]

OK, thanks. Just fascinating about how so many people experience differing things about what does or doesn’t work. Just a lot of moving parts and possible things they may have installed or enabled, whether relative to CF or in general, I guess. Glad it worked for you.