Microsoft Access is a relational database management system (RDBMS) developed by Microsoft. It’s part of the Microsoft Office suite, included in the Professional and higher editions or sold separately.
Microsoft Access provides a quick and easy way to develop databases and is particularly well suited for individuals and small business. Access is the most popular desktop database on the market.
In Access, most tasks can be performed via the graphical user interface (GUI), but more advanced users can also develop applications using Visual Basic for Applications (VBA) code. Advanced users can also use SQL code to write or modify queries.
Some of the main tasks performed in Access include:
- Create and maintain databases and their tables.
- Set up rules and restrictions over the type of data that can be entered into the database.
- Create and run queries.
- Create forms to allow non-technical users to enter data, search the database, and perform other tasks.
- Create reports so that the data can be presented in a more readable format, or emailed, printed out, etc.
- Create macros so that multi-step tasks can be performed with the click of a button (or even upon opening the database).
- Import and export data from external sources (such as Excel spreadsheets, .csv files, etc)
Benefits of Access
Some of the main benefits of using Access, when compared to some of the more “enterprise level” database management systems, include:
- Provides a quick and efficient way for non-technical users to create and maintain a database. No need to hire programmers or database developers every time you want a new database, and no need to hire a DBA to look after the database system.
- Easy to learn – especially for users accustomed to the Microsoft Office suite of products. Access provides “wizards” for some of the more involved tasks. These wizards guide you through the steps of performing a particular task, and can be invaluable for the first-time user who might feel a bit intimidated by it all.
- No coding required. Most tasks can be done by “point-and-click” via the GUI.
- Provides a quick and efficient way for those users to generate reports for management, clients, etc
- The inbuilt forms functionality allows non-technical users to provide their own interface for the database, therefore eliminating the need for an application to be built.
- Thousands of free templates are available for common (and not so common) database designs. No need to start from scratch if you need a more advanced database – simply download a template and modify as required.
Limitations of Microsoft Access
While it can be the perfect choice for many scenarios, Access does have its limits. It was never intended as an enterprise solution. Access’ strengths lie in providing individuals and businesses with an efficient way to store and retrieve data and generate useful reports without needing to hire developers or get the IT department involved.
For enterprise level applications – such as a corporate CRM – a more sophisticated system such as SQL Server would be a better choice. SQL Server is a database server, and is designed to have many users accessing it at the same time. SQL Sever databases can also hold a lot more data than Access databases. SQL Server has more advanced security features, more optimisation options, networking features, and more.
Here are two examples of the limitations of Access vs SQL Server:
- Database size: Access databases can have a maximum size of 2 gigabytes (minus the space needed for system objects). SQL Server databases can have a maximum size a maximum size of 524,272 terabytes with a maximum data file size of 16 terabytes.
- Simultaneous users: An Access 2016 database has a limit of 255 concurrent users, whereas each instance of SQL Server 2016 allows a maximum of 32,767 simultaneous user connections.
In practice, these numbers would probably be much lower. An Access database might run into trouble with 1 user, 10 users, or 100 users, depending on its design.
Mind you, the same is true for SQL Server. One poorly designed database could bring the whole database server crashing down. However, the key point is, SQL Server is more robust and scalable than Access.
But even if its not an enterprise level application, sometimes SQL Server is still a better choice. For example, most websites use database servers such as SQL Server or MySQL, even if its a small website with low traffic.
This makes the website more scalable. If it suddenly attracts a lot of traffic, it should be able to handle it.
Also, for smaller websites on shared hosting accounts, it’s easier for the hosting provider to use SQL Server or MySQL because they can have many hundreds, or even thousands, of websites accessing a single database server. Plus even a small website can end up with huge amounts of data in its database over time.
Custom Web Apps
More recent versions of Access have allowed the user to create web applications for their Access databases.
Access 2010 introduced the ability for databases to be published to SharePoint 2010 web sites running Access Services, and uses SharePoint lists to store its data.
Access 2013 introduced “custom web apps”. The web apps can be created from within Access, and when the app is saved, it is saved directly into the SharePoint 2013 server running Access Services or Office 365 site. Access 2013 web solutions store its data in an underlying SQL Server database.