Nine Reasons NOT To Use MS Access To Power A DB-Driven Website

On one hand, everyone keeps asking me to compare MS Access to SQL Server. On the other hand, scores of people are sending me feedback asking me to help fix MS Access databases that have been corrupted or that used to work but don't work anymore.

The answer to both questions that I usually respond with is...

If you care about your data, don't use MS Access...

Unfortunately, that answer isn't good enough for anyone (although I found it precise and completely right-on) because it doesn't include any good reasons why you shouldn't use Access. So here's your reasons... in no particular order.

1. MS Access wasn't designed to be used in an intensive database-driven environment.

   Using Access in this type of environment will always lead to database corruption and loss of data over time. I experienced this first-hand with the ASP Emporium... After the first 7 months, I had replaced the db about 10 times. Each time, it worked for a few weeks and then I would get some bizarre OLEDB error: Unspecified Error. When trying to open the DB each time, it would be corrupted and sometimes would not open. In the end, it's hard to get around the fact that Access was meant for one or maybe a few users at the most to interact with the db at the same time. On the other hand, SQL Server is optimized for dozens if not hundreds of simultaneous users working with the same data at the same time.

2. MS Access isn't secure.

   Even a workgroup protected db is not secure. I've seen code written in higher level languages that have the ability to guess MS Access passwords. Besides that, most Access DB's are stored somewhere in public server space meaning that they can be downloaded by anyone if found... Another problem that may crop up because the db is in public server space is, the db could be altered or deleted accidently by a programmer using the FileSystemObject (oops, but it happens)... Doing a simple search for MS Access password cracking software on Google will yield you with several applications designed for that specific purpose. So much for security and MS Access... Conversely, SQL Server is secured by controlled access via either Windows NT challenge and response architecture using logon accounts controlled by Windows or via SQL Server controlled user accounts secured with passwords. Accounts can be limited to certain databases or to certain pre-defined roles within the sql server. Additionally, SQL Server can be configured to encrypt all data sent to and from the server to all clients requesting database information, adding yet another layer of security to the system.

3. MS Access 2000 only handles simple stored procedures. 97 doesn't support them at all...

   MS Access provides no interface to allow anyone to view stored procedures, you'll need to build one... A stored procedure can be inputted programmatically with the ADO objects in an Access 2000 db though these procedures can only be the of the simplest kind. Attempting to use a procedure that doesn't start with INSERT, SELECT, UPDATE or DELETE, directly after the AS keyword, will result in a JET database error... For example, this stored procedure won't work in Access even though it is completely valid:

   CREATE PROCEDURE foo1 (@m1 int, @m2 char(10))
   AS
   DECLARE @m3 bit
   IF (@m1 > 0 AND @m1 < 26)
   BEGIN
   	SET @m3 = 0
   END
   ELSE
   BEGIN
   	SET @m3 = 1
   END
   
   SELECT
   field
   FROM
   table
   WHERE
   bit1 = @m3
   AND
   name = @m2
   ORDER BY
   name
   

   SQL Server will let you do anything in a stored procedure that can be done with the TSQL language. In addition, SQL server 2000 also supports user-defined functions which work similarly to stored procedures.

4. T-SQL isn't fully supported in Access.

   An SQL statement like this that joins three tables using outer joins will fail in MS Access, although it is a valid SQL statement and works on sql server... Access is full of pitfalls like this.

   SELECT
   a.a1, b.b1, c.c1
   FROM
   a
   LEFT JOIN
   b
   ON
   a.a2 = b.b2
   RIGHT JOIN
   c
   ON
   b.b3 = c.c2
   WHERE
   c.c1 = 1
   

   Also, triggers, procedures, etc... are hard to create in Access. If you can get them in there and recognized, you'll never be able to see them or work with them in the provided interface... Finally, MS Access data types are weak and extremely general in nature, making them less than suitable for certain tasks. I don't think I have to say anything about what SQL server can do with TSQL for this one. Bottom line is, when you start getting hardcore with your SQL, MS access will start crapping out.

5. MS Access is missing key functionality.

   Many system tables and stored procedures that are essential and extremely useful to someone attempting to work with a database programmatically don't exist in MS Access... A couple do, like @@IDENTITY though... However, you are missing all of the useful system tables and supporting stored procedures useful for database discovery, such as sysobjects and sp_helptext, sp_help, sp_depends, sp_rename, sp_columns and about 30 other system stored procedures and tables which I regard as essential for database development. All of these procedures and tables are part of SQL server and available from every database. SQL Server also has extended stored procedures which are compiled dll add ons written in C++ to allow SQL server to work with the server's file system or to extend the functionality of SQL server into new and exciting areas.

6. MS Access cannot do bulk inserts, updates or deletes.

   Now-a-days, its all about speed when it comes to databases and asp (see #8 below). Consider the following code which attempts to add 5 new records to a table called table. This will run when communicating with sql server but not with MS Access. As you can see, 5 sql statements are executed at once, pipelining the execution.

   dim c, sql
   
   sql = sql & "INSERT INTO table (field1) VALUES('value1')" & vbcrlf
   sql = sql & "INSERT INTO table (field1) VALUES('value2')" & vbcrlf
   sql = sql & "INSERT INTO table (field1) VALUES('value3')" & vbcrlf
   sql = sql & "INSERT INTO table (field1) VALUES('value4')" & vbcrlf
   sql = sql & "INSERT INTO table (field1) VALUES('value5')" & vbcrlf
   
   Set c = createobject("ADODB.Connection")
   c.open "connstring"
   c.execute sql
   c.close
   Set c = nothing
   

   To get that to work with access, it must be rewritten like this:

   dim c
   
   Set c = createobject("ADODB.Connection")
   c.open "connstring"
   c.execute "INSERT INTO table (field1) VALUES('value1')"
   c.execute "INSERT INTO table (field1) VALUES('value2')"
   c.execute "INSERT INTO table (field1) VALUES('value3')"
   c.execute "INSERT INTO table (field1) VALUES('value4')"
   c.execute "INSERT INTO table (field1) VALUES('value5')"
   c.close
   Set c = nothing
   

   Guess which one is faster. Since SQL server's TSQL parsing engine and execution platform is very robust, you'll often be amazed at how many sqls you can pump into it at once (i've done as many as 30,000 sqls at once) and still rest assured that it won't garble or misunderstand your commands - you are limited by only available memory and processing power. The same cannot be said for MS Access. Access is notorious for choking on even the most simple INSERT, UPDATE and DELETE commands - especially if more than 1 person is using the db at one time.

7. It's tough to access an MS Access database that isn't on a machine directly attached to your server.

   Since MS Access wasn't designed with this task in mind, you'll find yourself chewing your fingernails at 3:35 in the morning trying to get into an access db if it isn't on your physical machine. On the other hand, SQL Server listens on port 1433 and your database can be accessed anywhere on Earth, with the proper user account credentials of course.

8. MS Access is as slow as a database can get.

   MS Access contains none of the optimizations, memory management and usage, and processor options that SQL server does. Don't be surprised when your site visitors quit going to your site because they're tired of waiting for 45 seconds for the results of a SELECT query against MS Access. Also, since MS Access doesn't support bulk sql execution, like sql server, don't expect any speed increases with INSERT, UPDATE or DELETE statements either (see #6 above). One of the major reasons for this slag is due to the fact that MS Access doesn't support clustered and non-clustered indexes.

9. And finally, here is the biggest reason of all...

   Now that ADO.NET is set to eventually replace the COM ADODB libraries, you're going to have to use completely different sets of classes and code within the System.Data namespace for MS Access than you would for SQL Server. In fact, ADO.NET was designed with SQL Server in mind. If you haven't guessed what this means to me (and indirectly, you) at ASP Emporium, it means that I won't be supporting MS Access anymore at all with any of my database driven examples. Consider that a warning. Once C# apps start rolling off my assembly line, if they use databases, they'll only use SQL Server. There will be zero support for using MS Access with .NET from me...

I'm not saying that MS Access is a bad database, I'm just saying that MS Access has it's place and it's not on the Web powering your site... And in the end,

If you care about your data, don't use MS Access...