Highlighted

create data source for microsoft access on 64 bit server and ColdFusion 16

New Here ,
Apr 11, 2016

Copy link to clipboard

Copied

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.

TOPICS
Database access

Views

6.6K

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

create data source for microsoft access on 64 bit server and ColdFusion 16

New Here ,
Apr 11, 2016

Copy link to clipboard

Copied

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.

TOPICS
Database access

Views

6.6K

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
Apr 11, 2016 0
Advocate ,
Apr 11, 2016

Copy link to clipboard

Copied

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

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
Reply
Loading...
Apr 11, 2016 1
Adobe Employee ,
Apr 11, 2016

Copy link to clipboard

Copied

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

Thanks,

Vikram

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
Reply
Loading...
Apr 11, 2016 0
New Here ,
Apr 11, 2016

Copy link to clipboard

Copied

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.

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
Reply
Loading...
Apr 11, 2016 1
Contributor ,
Jul 03, 2017

Copy link to clipboard

Copied

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

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
Reply
Loading...
Jul 03, 2017 0
Community Beginner ,
Jul 05, 2017

Copy link to clipboard

Copied

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.

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
Reply
Loading...
Jul 05, 2017 0
Contributor ,
Jul 07, 2017

Copy link to clipboard

Copied

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.htmlpage_cf_admin.jpg

Thanks for any help and suggestion.

Pierre.

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
Reply
Loading...
Jul 07, 2017 0
Community Beginner ,
Jul 14, 2017

Copy link to clipboard

Copied

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

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
Reply
Loading...
Jul 14, 2017 0
New Here ,
Mar 01, 2019

Copy link to clipboard

Copied

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  

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
Reply
Loading...
Mar 01, 2019 0
New Here ,
Jun 13, 2017

Copy link to clipboard

Copied

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.

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
Reply
Loading...
Jun 13, 2017 0
Explorer ,
Sep 21, 2017

Copy link to clipboard

Copied

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

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
Reply
Loading...
Sep 21, 2017 2
Contributor ,
Sep 22, 2017

Copy link to clipboard

Copied

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.

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
Reply
Loading...
Sep 22, 2017 0
Adobe Community Professional ,
Jan 05, 2018

Copy link to clipboard

Copied

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.

/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
Reply
Loading...
Jan 05, 2018 0