As you may remember from my previous post, I had issues with Microsoft Access Data Sources not working after upgrading from CF8 to CF9. The reason being that CF9 decided to install the ODBC service on a different port.

Well after upgrading cfmldeveloper to CF9.0.1 I had the same problem, users started to report MSAccess DSN's were not working, except this time the service was running fine and on the correct port. This was a real head scratcher, and as is often the case with my issues they are fairly unique/unusual and so there often is nothing on google and even my fellow cfgurus cannot help me.

After further investigation I discovered that existing MSAccess DSN's were actually still working, it was only new DSN's that did not work, giving the following error.

[Macromedia][SequeLink JDBC Driver]TCP/IP error, connection refused. 

So I then went into the ColdFusion Administrator to take a look at the DSN's and was unable to do so, getting this 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

As you can imagine this was a bit confusing as the service was indeed running otherwise the old DSN's would not be working.

So I had a look in the <cfusion root>/lib/neo-datasources.xml (this is where the DSN's are stored) and found the cause, The CFADMIN was using the wrong port to connect to the ODBC service and for creating DSN's, it was again using 20000 instead of 19999 which result in the following JDBC URL.

<var name='url'><string>jdbc:sequelink:msaccess://localhost:20000;serverDatasource=dofficers</string></var> 

Now bear in mind that I am creating DSN's via the HELM control panel via my API, which is the only reason that was even possible, as obviously it was not possible to add a new MSACCESS DSN via the CFADMIN for the same reason.

So this again seemed like a trivial solution, just change the port again right? So as I know the setting had to be in one of the neo XML files I simply did a search for "20000" thinking this would give me what I want, but no, I came up empty and have now spent almost a month trying to resolve this.

So I viewed the source in the cfadmin for the msaccess.cfm page, and there I could see a hidden form field that contained the port number. My next step was to decrypt the CFADMIN and try and find out where the port came from. This also turned out to be a less than simple task as none of the decrypt tools would work for me. It was then suggested to me by fellow cfguru Mark Kruger that I needed to get a copy of the CFADMIN from ColdFusion 6 as the encryption had changed since then, and these tools had not since been updated.

I eventually got the files I needed and worked out that the port number came from the service factory, so again I was confused as I was sure the service factory settings came form the neo xml files? Well this is where I feel like a dumbass, I just got confirmation from Mike Nimer (who actually wrote some of the cfadmin) that I should be looking in the neo xml files, so I searched the files again and this time I got a hit in the neo-drivers.xml. So it was there all along, god knows how I missed it last time, maybe windows search screwed up or maybe I typed 2000 instead of 20000 several time sin a row LOL. Anyway the solution was indeed as easy as I had first thought, just open up the neo-drivers.xml and find the following.

<var name='port'><string>20000</string></var>

and simply change the port to 19999, then restart CF and everything should be good.

So why am I having these issues every time I update CF? I think it is because of the fact that when you install a new version of CF it leaves the previous version intact and simply disables the services (or should do at least). And in my case the cf installer is seeing the previous CF version and thinks it is still active and thinks there will be a conflict on the ODBC service port, so is changing it. Obviously in both cases it did not do it properly, the first time it only change the service port and not the service factory settings, the second time round it did the opposite.

Now I know some people will just say and did say  "why didn't you just use the Microsoft Access with Unicode" driver instead, and yes this would be a workaround as this driver does not rely on the ODBC service so is better anyway. But Unfortunately the HELM control panel we use only supports the regular MSACCESS DSN, so I needed to get this working again or no-one could use MSACCESS.
And yes we all know that people shouldn't use MSACCESS for production web sites, but they do.

Views: 6408