When Written: Feb 2006
There are times in the life of a web designer / application programmer when, because of either the client’s requests or the setup of the hosting web server, that they are forced to program for a technology which would not be their first choice. Nothing would make me use Access on a web site; let’s face it Microsoft doesn’t recommend that you do, and with the web licence for Microsoft SQL Server Express or MySQL both being free there is normally little point in going down the Access route for any web site that is expected to get more hits than a ‘Big Brother’ runner up. So it was with much swallowing of pride, a long string of garlic and a large mop to clear up the blood off the floor that I embarked on a live web site that would use Access.
What inspired this change of heart? Well it was for no other reason that the site needed to go live soon and the only web server that could be used was a shared one at an ISP’s rather than one of my own servers. The reason for this choice of server is far too complex to explain here, but involved large doses of politics, a fair measure of paranoia and a considerable lack of time to get a new server configured and up and running. The initial web site would only be using the Access database to gather data rather than feed dynamic pages and so the performance should not be too much of an issue. So, firing up my trusty copy of Dreamweaver MX2004, yes, yes I know there is now version 8 but I find MX2004 more productive at the moment and slightly more stable, although at times its ‘stability’ means that using Dreamweaver can be like balancing on a unicycle on top of a large ball. However, on this occasion all was going well. I created a connection string to connect to the Access database as he has done on numerous occasions but to a SQL server. Just to make sure that all was well, a click on the ‘Test Connection’ button showed that the connection string was working fine. The database showed in the ‘database’ tab of the application window in Dreamweaver, everything seemed well until I went to expand out this object to view the tables, nothing appeared. Now the database defiantly had tables, I put them there! Could it be permissions? Some more tests, which involved changing the permissions to dangerously open levels, made no difference. So in situations like this, what do the professionals do? We Google it! A search revealed that I wasn’t the only one to have this problem, but the answer wasn’t easily forthcoming, even a search on the normally excellent Experts-Exchange (www.experts-exchange.com) did not help. Eventually after a day or so on this problem and with me seriously considering hand coding this part of the web site, an article in the Macromedia Coldfusion knowledgebase seemed to point to an answer (Ref. 179021)
Now I do not use Cold Fusion but as this seem to describe the problem, and the fix seemed to be related to some new scripts that Dreamweaver uses, so it seemed to be worth a try.
According to the article it seems that the problem occurs only with a specific setup, this being when using a local ASP.NET or ASP test server running on XP SP2. This was not quite the setup that I was using; whilst his workstation is XP SP2 the test server is a windows 2003 ASP.NET web server but still, the fault described was exactly the problem that I was having. It appears that the problem lives in the _mmServrScripts that Dreamweaver uses to do lots of its clever database stuff. The procedure to fix this problem is to download the ‘Dreamweaver Extension Fix’ then for each of your sites, in Dreamweaver go to ‘Site | Advanced | Remove Connection Scripts’ then quit Dreamweaver. Next run the extension fix that you have just downloaded and then re-open Dreamweaver. Finally you will have to remake the database connection strings. After doing all this I was rewarded with a view of the tables with their fields in the application window and was able to continue developing the web site which went live two days later. Quite why this problem and its subsequent patch aren’t more obviously displayed on the Macromedia web site or made part of a service patch who only knows?
Article by: Mark Newton
Published in: Mark Newton