MS AccessMicrosoft Access is provided with Microsoft Office Professional and as such is free for those that have that software anyhow. But what makes ...
MS Access
Microsoft Access is provided with Microsoft Office Professional and as such is free for those that have that software anyhow. But what makes MS Access attractive compared to the other advanced database systems?
1. Easy to deploy. No database server is required on the client machines. Simple copy the database file (.MDB) and you are done. Note that the client must have the same or later version of Access installed or install the free Access Runtime environment.
2. As a file based database it is very easy to copy and make backups.
3. It has a very simple interface that allows you to develop forms and reports quickly and easily. Either,
use a “wizard” or drag and drop.
4. Combined front and back end. MS Access software provides all the features in one package – design tables and queries then add forms and reports, then all in the same software.
5. Rapid Application Development, Access allows you to create a solution quickly.Access solutions often require significantly less code than alternatives. It's an effective platform for prototyping.
6. For the novice programmer MS Access is ideal. It has a powerful database engine as well as a fully featured language in VBA. It can start simple and be extended to be a fully functioning application.
The main limitation of MS Access is that it cannot handle large numbers of users very well. A combined Access database (front end and back end together in one file) is pushing the limits at about 8 users.A split design is more stable and can support 50 to 100 users with clever programming (caching data locally etc.).
SQL Server
Microsoft’s SQL Server is the big brother of MS Access.While Access is targeted at the smaller organization SQL Server is targeted at the medium to large organizations with a range of versions to suit.
The benefits of SQL Server are:
1. Fully scalable. From the free version (SQL Server Express) to the Enterprise Edition you have a straight forward upgrade path and unlimited potential.
2. Powerful. With features such as stored procedures, user defined functions, referential integrity, triggers and so on you will be able to develop fully fledged, fast and powerful applications.
3. Secure. With SQL Server you can use transaction logs, automated backups, replication and transactions to ensure that your data is never lost and you can recover from disaster.
4. Centralized. SQL Server keeps all of the data in one place and you can attach any front ends you like. Your database may have a collection of windows forms applications, web applications even MS Access applications attached – all sharing the same data and ensuring consistency.
One downside of SQL Server is its complexity. It can be difficult to set up particularly the advanced features such as automated backup, log shipping and replication. It can also prove difficult for the novice developer to work with its disconnected model and lack of wizards to simplify development.
Additionally, SQL Server is purely a Database Server, it does not provide any User interface programming features like Forms, Reports so you will have to develop a User interface with another tool.
Summary
At the end of the day it is a question of “horses for courses”
You have to decide which is more suitable for your requirements.
If you decide to go with MS Access, you can always upscale the data to SQL Server later on.
