I also think that it applies to any application that requires security and
stability. Access is for non programmer to catalog their CD collection.
Put mission critical data in a real database like SQL Server, Oracle or
MYSQL.
Rick
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 2:08 PM
To: CF-Talk
Subject: RE: MS Access as a backend database
> Only the ODBC driver is used on a flat .mdb file, and only
> ONE user at a time accesses the database:
> ColdFusion is the user. This driver is just as good as the
> Oracle or SQL Server driver.
There are plenty of problems with using Access for web applications. While
it may work in some cases, such as Ray's Death Clock, it will certainly not
work acceptably in many other cases.
The problems with Access have nothing to do with the database driver itself,
although, under CFMX, people typically use SequeLink to connect CFMX's JDBC
drivers to the ODBC driver used by Access, which can't help performance any.
These problems also have nothing to do with the Access desktop application
itself, which is simply a GUI used to work with Access databases.
There are several significant problems with Access, which I'll list in no
particular order. First, Access files are typically locked by the
application server, and they typically can't be modified while the
application server is running.
Second, Access files must be directly accessible by the CF server's
filesystem, which is not very secure. If your CF server is compromised, so
is your data. If you have any "due diligence" security requirements, Access
simply won't meet them.
Third, Access doesn't automatically reclaim unused space. When you delete
records, Access doesn't put new records where the old ones were, it just
grabs more space for new records. As a result, within a sparsely populated
database in which deletions are frequent, Access might use a tremendous
amount of space for hardly any records. A prime example of this is when you
use Access to store Client variables within CF. I encountered a situation
where a ~2GB Access database had about 200 records total. Needless to say,
every page request was excruciatingly slow!
Most significantly, Access isn't designed to handle multiple concurrent
connections very well. The default granularity of locking within Access is
table-level, if I recall correctly. This means, you may have a table with
one million records (Access can actually handle large numbers of records
with no problem), but if someone's inserting a new record, you won't be able
to touch any records within the table until the insert is complete.
This isn't to say that Access isn't suitable for any web applications, or
that it's an inferior product. It's simply not designed to be a
transactional database for applications that support multiple concurrent
users. To the degree that you can get away with using it as such, good for
you! However, you should expect - and prepare for - the worst.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
