Solved

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

Posted on 2016-09-30
11
70 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now