Microsoft Access and Microsoft SQL Server are both database applications. The major difference between the two is in how the software is used. Microsoft Access is used in home or small business applications. Microsoft Access is not able to handle large quantities of database calls. Microsoft SQL Server is for medium to large businesses that need a solution for better data processing. Both these software applications have advantages and disadvantages.
Interface
Video of the Day
The Access interface is mainly for end users unfamiliar with more complex database interaction. SQL Server Management Studio does not offer the forms and drag-and-drop query creation that Access uses. Access allows users to create tables and queries by manipulating icons and using wizards. SQL Server is more for the expert and only gives the user a command line interface, so it is less intuitive and takes a longer time to learn.
Video of the Day
Price
Microsoft Access is a part of the Office Suite available at any software store. Microsoft Access is an affordable solution for small businesses that do not need millions of records for storage. It is also good for the individual who wants to run reports for a freelance consultant business. SQL Server is a large enterprise solution used by multi-million dollar companies, so it is understandably more costly than Access. A standalone MS Access license is approximately $200 while a SQL Server license is several thousand dollars.
Queries
Queries in Access have some different keywords in the syntax compared to SQL Server. For instance, Access uses an "IIF" statement. The "IIF" statement is similar to the "CASE" statement in SQL Server, but the syntax and structure are different. People who are familiar with Access will need to learn additional syntax to migrate to SQL Server.
Tables
Microsoft Access and SQL Server both support relational tables. Relational table design allows users to link data from one table to another. It also prevents orphaned records, which happens when a linked table record is deleted without its counterpart. For instance, a relational table of customers could not have a record deleted if the order record is not first deleted. SQL Server has one added ability in that it supports temporary tables. Temporary tables are structures made on-the-fly and deleted when the user is done with the calculations.
Recovery
An Access file can only be recovered from corruption or data failure if the database was backed up to another drive. SQL Server supports automated backup procedures to a network drive or media disk. SQL Server also allows programmers to rollback procedures using transaction logs. Access does not use transaction logs.