Compare SQL Server and MS Access

Advantages of MS Access

MS Access is a light weight database managements system. Also, it provides it's own

designer so that you can generate reports and simple windows applications without

using another programming environment.

It is easy to deploy an MS Access database. Just copy the .MDB file to appropriate

location and you are done. There is no database attach/detach process.

Which one to choose - MS Access or SQL Server ?

MS Access is ideal only for applications that require very small databases. Even

though the maximum database size limit for MS Access is 1 GB, the performance

becomes poor if the size grows more than few hundred MBs.

If you expect your application database to grow more than 100 MB in future, you

should consider using SQL Server. It may not be an easy task to migrate to SQL

Server later. Eeven though SQL Server allow you to import your MS Access database to

SQL Server, you will have to do lot of code changes to take advantage of the SQL

Server features.

There will be major performance difference between MS Access and SQL Server. If you

are writing a critical application where performance is important, then you must

consider SQL Server instead of MS Access.

The new SQL Server 2005 Express is a free version and allow copy/deploy feature. You

can copy the database file and attach on the fly. It is a good idea to use SQL

Server 2005 Express if you cannot afford to purchase SQL Server license. It has much

better peformance and features compared to MS Access.

The bottom line is, choose MS Access only for small and non critical applications.