asked on

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

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 :

Windows 10 64 bit OS
Coldfusion 2016 64 bit Developers edition (installation of 32 bit version was unsucessfull - coldfusion service would not launch)
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
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.

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

_agx_

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?anchorAnswerId=33734615#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

jameskane

ASKER

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

_agx_

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).... :

jameskane

ASKER

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

_agx_

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.

jameskane

ASKER

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

jameskane

ASKER

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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

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

jameskane

ASKER

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

_agx_

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 ;-)