Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Coldfusion 2016 setup problem with MS Access (advice needed).

Posted on 2016-09-30
11
Medium Priority
?
522 Views
Last Modified: 2016-10-04
I am trying to set up Coldfusion 2016 - Developers edition  to support evolution of  somewhat ancient Coldfusion production system -which will be replaced within the next few years. While I have successfully set up and used Developers editions 9 and 10  I am in difficulties with 2016 developers edition. The platform I need is :


  1. Windows 10 64 bit OS
  2. Coldfusion 2016 64 bit Developers edition (installation of 32 bit version was unsucessfull - coldfusion service would not launch)
  3. MS Access 32 bit (from Office 7) -- I know that's ancient, but that's the reality - production system is scheduled for upgrade 2018 to use mysql
  4. Dreamweaver.

I believe I have correctly installed the 32 bit  ODBC drivers (64 bit drivers not being available from MS) using the Data Source  administrator found at c:\windows\sysWOW64\odbcad32.exe.  

However, I am unable to connect with the coldfusion administrator as you will see from the error messages 1 and 2 -  attached.

first error in working with coldfusion administratorsecond error in working with coldfusion administrator
My feeling is that the problem lies  in  trying to use the ms 32 bit access driver with the current 64 bit version of coldfusion (2016).

I speculate that the problem would have been solved if the 32  bit version of 2016 had installed - but as mentioned above  -  the installation looked ok, but the coldfusion service would not launch.

Appreciate your  opinion on this  situation - is the configuration I am seeking impossible given Adobe's lack of support for MS access  ?
OR am I missing somthing fundemental and you can give me a few pointers to help sort it out.

Many thanks

jim
error2.JPG
0
Comment
Question by:jameskane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 41823672
Disclaimer, I haven't used Access in a long time, but the 32 vs 64 driver is almost definitely the problem.

Try using the "Microsoft Access with Unicode" driver instead.  When you first create the datasource, it's one of the 2 "Access" options in the Driver list. There are some slight differences in query behavior, but I usually have better luck with that driver than battling with ODBC.  See here for instructions

https://www.experts-exchange.com/questions/26488825/Coldfusion-Access-ODBC-Error.html#a33734615

If not, maybe the tips on this thread will help? It worked for CF11, but YMMV for CF2016 / Win10.

https://www.experts-exchange.com/questions/28767919/Error-in-creating-Datasource-in-Coldfusion-11.html
0
 

Author Comment

by:jameskane
ID: 41824654
Thanks very much for the pointers _agx_ !!

Been plowing through the information and it looks like others have had this problem in the past ! Apparently the solution is get the coldfusion administrator to include unicode as one of the drivers. The
My installation of 2016 does not have unicode in the dropdown menu - so my focus is on trying to achieve this.

I am attaching the neo-drivers.xml file from the coldfusion library.  Its hard for me to understand, but it does seem that unicode is included - but why then does it not appear in coldfusion library menu ?

<wddxPacket version='1.0'><header/><data><array length='3'><struct type='coldfusion.server.ConfigMap'><var name='MySQL5'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>3306</string></var><var name='vendor'><string> MySQL</string></var><var name='class'><string> com.mysql.jdbc.Driver</string></var><var name='handler'><string>mysql5.cfm</string></var><var name='url'><string>jdbc:mysql://[host]:[port]/[database]?tinyInt1isBit=false&amp;[args]</string></var><var name='name'><string>MySQL 5</string></var></struct></var><var name='SybaseJConnect5'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>5000</string></var><var name='vendor'><string>Sybase</string></var><var name='class'><string>com.sybase.jdbc2.jdbc.SybDriver</string></var><var name='handler'><string>sybasej.cfm</string></var><var name='url'><string>jdbc:sybase:Tds:[host]:[port]/[database]?[args]</string></var><var name='name'><string>Sybase jConnect 5.0</string></var></struct></var><var name='ODBCSocket'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>20006</string></var><var name='host'><string>localhost</string></var><var name='vendor'><string>DataDirect</string></var><var name='class'><string>macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>odbcsocket.cfm</string></var><var name='url'><string>jdbc:sequelink:odbcsocket://[host]:[port];serverDatasource=[datasource]</string></var><var name='name'><string>ODBC Socket</string></var></struct></var><var name='Oracle'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>1521</string></var><var name='vendor'><string> Macromedia</string></var><var name='class'><string> macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>oracle.cfm</string></var><var name='url'><string>jdbc:macromedia:oracle://[host]:[port];SID=[sid];AuthenticationMethod=userIDPassword;sendStringParametersAsUnicode=[sendStringParametersAsUnicode];querytimeout=[qTimeout];[args]</string></var><var name='urlmap'><struct type='coldfusion.server.ConfigMap'><var name='supportLinks'><string>true</string></var></struct></var><var name='name'><string>Oracle</string></var></struct></var><var name='Apache Derby Client'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>1527</string></var><var name='vendor'><string>Apache</string></var><var name='class'><string>org.apache.derby.jdbc.ClientDriver</string></var><var name='handler'><string>derbyClient.cfm</string></var><var name='url'><string>jdbc:derby://[host]:[port]/[database];[args]</string></var><var name='name'><string>Apache Derby Client</string></var></struct></var><var name='MSAccessJet'><struct type='coldfusion.server.ConfigMap'><var name='port'><string></string></var><var name='vendor'><string> Macromedia</string></var><var name='class'><string>com.inzoom.jdbcado.Driver</string></var><var name='handler'><string>msaccessjet.cfm</string></var><var name='url'><string>jdbc:izmado:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[databasefile];IzmJdbcEsc=yes;IzmReleaseOnClose= no</string></var><var name='name'><string>Microsoft Access with Unicode</string></var></struct></var><var name='Sybase'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>5000</string></var><var name='vendor'><string>Macromedia</string></var><var name='class'><string> macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>sybase.cfm</string></var><var name='url'><string>jdbc:macromedia:sybase://[host]:[port];DatabaseName=[database];SelectMethod= [selectmethod];sendStringParametersAsUnicode=[sendStringParametersAsUnicode];querytimeout=[qTimeout];[args]</string></var><var name='name'><string>Sybase</string></var></struct></var><var name='MSAccess'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>20006</string></var><var name='host'><string>localhost</string></var><var name='vendor'><string>DataDirect</string></var><var name='class'><string>macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>msaccess.cfm</string></var><var name='url'><string>jdbc:sequelink:msaccess://[host]:[port];serverDatasource=[datasource]</string></var><var name='name'><string>Microsoft Access</string></var></struct></var><var name='Other'><struct type='coldfusion.server.ConfigMap'><var name='vendor'><string>Other</string></var><var name='class'><string></string></var><var name='handler'><string>default.cfm</string></var><var name='url'><string>jdbc:[driver]:[database]:[args]</string></var><var name='name'><string>Other</string></var></struct></var><var name='OracleThin'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>1521</string></var><var name='vendor'><string> Oracle</string></var><var name='class'><string>oracle.jdbc.driver.OracleDriver</string></var><var name='handler'><string>oraclethin.cfm</string></var><var name='url'><string>jdbc:oracle:thin:@[host]:[port]:[sid]:[args]</string></var><var name='name'><string>Oracle Thin Client</string></var></struct></var><var name='DB2_OS390'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>50010</string></var><var name='vendor'><string>Macromedia</string></var><var name='class'><string> macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>db2os390.cfm</string></var><var name='url'><string>jdbc:macromedia:db2://[host]:[port];sendStringParametersAsUnicode=[sendStringParametersAsUnicode];[args]</string></var><var name='name'><string>DB2 UDB for OS/390</string></var></struct></var><var name='DB2'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>50000</string></var><var name='vendor'><string>Macromedia</string></var><var name='class'><string>macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>db2.cfm</string></var><var name='url'><string>jdbc:macromedia:db2://[host]:[port];DatabaseName=[database];sendStringParametersAsUnicode=[sendStringParametersAsUnicode];StripNewLines=true;querytimeout=[qTimeout];[args]</string></var><var name='name'><string>DB2 Universal Database</string></var></struct></var><var name='JDBC_ODBC_Bridge'><struct type='coldfusion.server.ConfigMap'><var name='vendor'><string>Sun Microsystems</string></var><var name='class'><string>sun.jdbc.odbc.JdbcOdbcDriver</string></var><var name='handler'><string>jdbcodbc.cfm</string></var><var name='url'><string>jdbc:odbc:[datasource];[args]</string></var><var name='name'><string> JDBC-ODBC Bridge</string></var></struct></var><var name='MSSQLServer'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>1433</string></var><var name='vendor'><string>Macromedia</string></var><var name='class'><string>macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>sqlserver.cfm</string></var><var name='url'><string>jdbc:macromedia:sqlserver://[host]:[port];databaseName=[database];SelectMethod=[selectmethod];sendStringParametersAsUnicode=[sendStringParametersAsUnicode];querytimeout=[qTimeout];applicationintent=[applicationintent];[args]</string></var><var name='name'><string>Microsoft SQL Server</string></var></struct></var><var name='Apache Derby Embedded'><struct type='coldfusion.server.ConfigMap'><var name='vendor'><string>Apache</string></var><var name='class'><string>org.apache.derby.jdbc.EmbeddedDriver</string></var><var name='handler'><string>derbyEmbedded.cfm</string></var><var name='url'><string>jdbc:derby:[database];create=[isnewdb];[args]</string></var><var name='name'><string>Apache Derby Embedded</string></var></struct></var><var name='MySQL_DD'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>3306</string></var><var name='vendor'><string>Macromedia</string></var><var name='class'><string>macromedia.jdbc.MacromediaDriver</string></var><var name='handler'><string>mysql_dd.cfm</string></var><var name='url'><string>jdbc:macromedia:mysql://[host]:[port];databaseName=[database];querytimeout=[qTimeout];[args]</string></var><var name='name'><string>MySQL (DataDirect)</string></var></struct></var><var name='PostgreSQL'><struct type='coldfusion.server.ConfigMap'><var name='port'><string>5432</string></var><var name='vendor'><string>PostgreSQL</string></var><var name='class'><string>org.postgresql.Driver</string></var><var name='handler'><string>postgresql.cfm</string></var><var name='url'><string>jdbc:postgresql://[host]:[port]/[database]?[args]</string></var><var name='name'><string>PostgreSQL</string></var></struct></var></struct><struct type='coldfusion.server.ConfigMap'><var name='interval'><number>420.0</number></var><var name='alter'><boolean value='true'/></var><var name='disable'><boolean value='false'/></var><var name='disable_blob'><boolean value='true'/></var><var name='create'><boolean value='true'/></var><var name='revoke'><boolean value='true'/></var><var name='disable_clob'><boolean value='true'/></var><var name='blob_buffer'><number>64000.0</number></var><var name='buffer'><number>64000.0</number></var><var name='select'><boolean value='true'/></var><var name='drop'><boolean value='true'/></var><var name='login_timeout'><number>30.0</number></var><var name='grant'><boolean value='true'/></var><var name='delete'><boolean value='true'/></var><var name='disable_autogenkeys'><boolean value='false'/></var><var name='pooling'><boolean value='true'/></var><var name='insert'><boolean value='true'/></var><var name='update'><boolean value='true'/></var><var name='storedproc'><boolean value='true'/></var><var name='timeout'><number>1200.0</number></var></struct><struct type='coldfusion.server.ConfigMap'><var name='adobedriverversion'><string>5.1.1 (Build 0001)</string></var></struct></array></data></wddxPacket>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 41824789
My installation of 2016 does not have unicode in the dropdown menu

Ugh... apparently a core DLL file isn't installed in the 64bit version :/. That's why the option isn't in the list.  This thread suggests a work around: copy the "izmjniado.dll" file from a 32 bit install ie {cf10_root}/cfusion/lib into {cf2016_root}/cfusion/lib/ . The unicode option should be listed after restarting CF.

Regarding the ODBC option, did you try ignoring the error as I mentioned here:

... This post mentions a similar bug in CF10.  For .mdb files, they suggest you ignore the error and create the DSN anyway.  Then (do these steps).... :
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jameskane
ID: 41824803
Yes, I have had that error in the past - but ignored it.  The connection had in fact been made correctly.  This time around I also ignored it, and the new source did appear on the list - like in the past. But, unlike the past when I tried to verify the connection it threw up the error in the image I attached.

Sorry, should have noted that .

Jim
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41824975
Updated:

and the new source did appear on the list - like in the past

1. Have you ever done it successfully on Windows 10 specifically (with any version of CF)?

2. Can you post a screen shot of your ODBC datasources? I'm wondering if the DSN's platform is "any" or "32bit". If it's not 32bit, that might be the problem. Try creating a 32bit DSN manually like sglee did here.  You shouldn't need to do that but .. it's worth a shot.
0
 

Author Comment

by:jameskane
ID: 41825366
Never succeeded with windows 10 - this is my first attempt.  I have succeeded with windows 7 using coldfusion 9 and 10. As I recall vers 9 never gave any "unable to update NT registry.." warning - just installed - just created the datasource correctly. Vers 10 gave me problems. I had to launch the Data source administrator from windows/sysWOW64/odbcad32.exe. I got the "unable to update NT registry" warning, but the datasouce got created correctly in any case.

Below are the 32 and 64 bit versions of the odbc administrator for my current windows 10 install. I only use the 32 Bit version, but interestingly enough there is a link to the 64, which gets populated automatically. This also occurrs on my fullyfunctional coldfusion 9 install

regarding sglee's manual approach - looked at that - ID: 41098969  - it seems to be what I have been doing since in installed version 10 (noted above) ?

Jim

odbc administrator 32 BIT64 BIT ODBC ADMIN
0
 

Author Comment

by:jameskane
ID: 41825451
https://forums.adobe.com/message/8676379#8676379

Stumbled into the above link which proposed a solution and gave the following information

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

It did give a process from ADOBE support which if followed should have solved the problem. Tried it and it did not work for me.

Looks like the end of the road..???
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 41825613
Never succeeded with windows 10 ... [ColdFusion] Vers 10 gave me problems.

Ok. I don't have the right environment to test it out so I was trying to see if there was something that definitively indicated the problem was CF2016 (only) or Windows 10 (only), but since you haven't done either yet, scratch that.

interestingly enough there is a link to the 64, which gets populated automatically.

Yes, that's what I'd expect. Remember it's what caused the original issue in CF10. ie CF automatically creates a 64 bit ODBC DSN, instead of a 32 bit DSN.

it seems to be what I have been doing since in installed version 10 (noted above) ?

My bad, I didn't realize you created the 32 bit DSN manually.

Download and install 64 bit MS Access Database Engine

Honestly, I'm not sure what the official policy is - support for Access has always seemed sketchy at best IMO.  Which one did you install? The thread seems to suggest the x64 version? If you did that already, unfortunately I'm out of ideas about the ODBC stuff.

      - AccessDatabaseEngine.exe
      - AccessDatabaseEngine_X64.exe

The only other thing I can think of would be to try a different driver.  I'll take a quick look.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41825663
A quick search for Access + JDBC driver turned up this one which seemed to work on Win7 64 bit.  http://ucanaccess.sourceforge.net/site.html

1. Downloaded version: UCanAccess-3.0.6-bin.zip
2. Copied these files into {cf_root}\WEB-INF\lib:
-  ucanaccess-3.0.6.jar
-  lib\commons-lang-2.6.jar
-  lib\commons-logging-1.1.1.jar
-  lib\hsqldb.jar
-  lib\jackcess-2.1.3.jar
3. Restarted CF
4. Created "Other" DSN:

       JDBC URL:  jdbc:ucanaccess://C://path//to//mydb//access2007.accdb
       Driver class: net.ucanaccess.jdbc.UcanaccessDriver
       Driver name: net.ucanaccess.jdbc.UcanaccessDriver
       Username:    Admin    
       Password:   (left blank)

Note - Most of the DSN values case sensitive
0
 

Author Closing Comment

by:jameskane
ID: 41825912
Many thanks  _agx_  .  While I still have not solved the problem - you have helped me understand what the causes are. Iooks like the ball is in the  Coldfusion 2016 court - maybe they will fix it in a future update.

So, its time to move on.... away from MS access !!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41826644
Yes, sorry I couldn't test any further without the right environ. However, the driver above is an option if all else fails.  Then at least you can still use cfquery with the Access db.  

        >> maybe they will fix it in a future update.

Maybe.. but I wouldn't hold my breath ;-)
0

Featured Post

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question