The thread I've chosen to examine this month is all about driving ColdFusion sites with Microsoft Access databases. Very often I hear developers state that "Access is evil," or that you'd have to be crazy to use Access on the back end of a Web application, but are these generalized statements warranted? The CFDJList discussion began with a post by Chelsey Gourgaud, who wrote asking whether you can use Access when building a ColdFusion site, whether it will fail under stress, what other implications this practice carries, and what alternatives are available and commonly implemented. I must admit that I was quite surprised by the usage load that Access is successfully handling when driving CF sites, based on what developers described in their replies to this post.

Will Swain replied to the post, reassuring Chelsey that he uses Access 2000 for a lot of sites, including one that receives around 100,000 visitors per week. The skeptical side of me immediately wondered whether that number is accurate as well as what percentage of these visits resulted in data being inserted into the database or any performance-intensive query being run. Rather than immediately ask Will about this, I decided to wait and see what other list subscribers had to say about their experiences with Access. I found out that Will is not alone.

Ray Thompson, a very active and knowledgeable subscriber, chimed in and stated that he has a site that queries indexed columns in a 500,000-record Access database table in about 20 milliseconds! I have to assume that the query is rather simple, and that it doesn't perform any "real" text searching (just AutoNumber and/or number fields), but this is still not only surprising, but impressive.

Ray did go on to list some of the limitations of Access. Specifically, it doesn't have support for stored procedures, has a one-gigabyte size limitation, doesn't have support for concurrent users, and has to be "known to the ColdFusion Server." Meaning that unlike enterprise databases, which can sit behind a firewall and "listen" for requests, Access has to sit in front of the firewall on the machine running ColdFusion. He warned of this security risk, and recommended setting the number of connections ColdFusion uses to connect to the Access database to two (in the ColdFusion Administrator) in order to deal with the concurrency issues.

Other members (myself included) warned Chelsey of the concurrency issues surrounding Access - that it was never intended for use by simultaneous users. Douglas Knudsen added to the discussion by recommending that Chelsey visit the Macromedia CFQUERY FAQ at www.macromedia.com/v1/handlers/ index.cfm?ID=22128&Method=Full.

A number of List members posted regarding their preferences and experiences with other databases. Many recommended going to SQL Server, or to MySQL if SQL Server isn't within the budget; some also recommended Oracle. As it turns out, Chelsey downloaded and installed MySQL to give it a try, which also sparked a thread from people interested in helping Chelsey properly configure MySQL datasources for use with ColdFusion.

All in all, we learned quite a bit about Access and about how ColdFusion handles database connections from this thread. I, for one, learned a lot about its popularity among ColdFusion developers. While I still advise running any Web site off an enterprise-level database, I can now state that there are people successfully running real-world applications off Access databases, and that Access may be a good short-term/low-budget database solution. Think about this next time, before you immediately begin preaching "the evils of Access," and join us next month for yet another installment of Tales from the List.