Small Business Software
Why MS Access?
SQL Server is a more robust database than MS Access - for instance it can hold more data, has better security and can handle a large user base. For simple systems MS Access is a better bet - the file is portable and can easily be moved around. For example a salesman can take a copy of a database to his laptop and take it on the road. All the code, screens, data tables etc are held in the one file.
Microsoft Access also speaks the same language as the other components in the MS Office suite. SQL Server does not have a language and will rely on external programs to connect to it....for example a screen will be written in Visual Basic.Net and will then connect to the external SQL Server database. MS Access on the other hand will have both the language and database built into the same file.
You can if needed develop your screens and SQL in MS Access and connect to SQL Server for the data storage. Why would you want to do this? Well, stability is one reason........SQL Server offers stable data storage and can handle hundreds of users. If your database is to be used over a wide area you would be better using SQL Server.
Microsoft Access is also a very good tool to clean data from other systems. Many older databases have their data stored in non standard formats. These can be exported to text files, but the output may still be unusable. MS Access can through use of queries and tables clean up the data in these text files and remove any unwanted characters so that the data is in an acceptable format. MS Excel is also another good tool for this, but personally I find MS Access easier to use.
Large corporations are more likely to use something like SQL Server to store their main data. They may also use MS Access to create smaller databases or to prototype new systems being developed.
MS Access is more likely be used by a small business with a small number of users.