Definition and Description: What is Microsoft Access?
Microsoft Access is a proprietary database management system (DBMS) that is part of the MS Office suite of productivity tools. It is sold either as a standalone utility or bundled as part of Office Professional and other premium editions of Microsoft’s business software suite.
Office 365 subscriptions include Microsoft Access, but it is restricted to “PC only.” The current version of Access (Microsoft Access 2019 as of this writing) is usually provided with an Office 365 account.
Microsoft Access uses the Microsoft JET (Joint Engine Technology) Database Engine that is also used for SQL Server Express and Visual Basic. Since Access is limited in capacity from a database size standpoint, a JET database can be “up-sized” to an SQL Server or Azure SQL product that is more suited to very large databases.
In addition to JET, Microsoft Access comprises application development tools and a graphical user interface, or GUI.
Microsoft Access is a Relational Database Management System (RDBMS), but it is also an application generator with all the tools required, such as templates, forms, queries and reports. It can also be used in a non-relational manner, hence the years-old argument about whether it is, in fact, an RDBMS or just a DMBS.
Microsoft itself highlights the fact that, with Access, you can create “database apps” that are highly customizable, and store data in SQL Server and Microsoft Azure SQL (separate subscriptions required.)
Microsoft Access: Key Features
Microsoft Access is typically used as an applications development platform for non-developers in a business environment. Reports and other apps can be generated by the user, and these apps can access data from a number of different resources, including its own database file, also called an Access desktop database.
It can also be used as a front end to manage larger databases stored on SQL Server and Microsoft Azure SQL Database, a fully-managed Database-as-a-Service offering.
Microsoft Access is also compatible or interoperable with a range of other Microsoft and third-party products. It can be used to import, export or link to data from other sources such as Salesforce, dBASE, Excel, text files and many more.
Microsoft Access can import from or link to the following resources (click image to open larger version in new tab):
Microsoft Access can export to the following resources (click image to open larger version in new tab):
Although the application itself is PC Only, the tables can be stored on a network and simultaneously accessed by multiple users without the fear of data being overwritten. The RecordLocks Property can be used to define how the program reacts to simultaneous updates by two different users.
However, this is only possible with Access database formats like .mdb and .accdb. The use of any other database will result in the RecordLocks Property setting defaulting to No Locks. Other settings include All Records and Edited Record.
For example, to lock an entire record page (Edited Record setting_ when a user begins to edit a record, the following property setting can be implemented:
Forms(“ProductSKU”).RecordLocks = 2
When to Use Microsoft Access: Specific Use Cases
Microsoft Access can be used in the following scenarios. Each use case refers to a specific problem that cannot be addressed with spreadsheet programs like MS Excel, and may or may not require the use of MySQL, SQL Server or Azure SQL Database at the back end.
Here are some use cases for Microsoft Access:
-
When using multiple data entry forms.
-
When you need to link to fields or attach files and graphics to records.
-
When multiple users typically access the same data simultaneously.
-
When you need to invoke events based on user action (mouse-over, click.)
-
When the number of users or amount of data does not justify using more expensive database services or products.
-
As a development platform for personal, business and web applications.
This is by no means a comprehensive list of use cases, but to help you decide whether or not to use Microsoft Access for your own small business, corporate division or just personal use, here are some of the key advantages.
Advantages of Using Microsoft Access
Low Level of Expertise Required to Install and Use
Microsoft Access offers a simple installation process, and there are ample help resources to get you started based on what you want to do. Templates are available on Microsoft’s website and also within the application itself. This is often a good place to start if you’re unfamiliar with forms, reporting or databases in general.
Wide Range of Databases Supported
Access can work as a front-end utility for a vast number of popular open-source and proprietary database products and services, including Microsoft’s own, as well as those of competitors. This interoperability is key to Microsoft Access being a popular front-end application. The appealing GUI and highly functional UX are added bonuses.
Adequate Storage Capacity
The 2GB capacity of a Microsoft Access database is more than enough for most small business requirements. If you need more, you can install Microsoft SQL Server 2008 R2 Express or higher, which have a database capacity of up to 10GB. Since Microsoft Access application tables can also be linked to an SQL Azure database, storage is not really of concern even if you plan to scale up considerably.
Multi-user Capability
There is a still-prevalent myth that Microsoft Access Jet databases can be used by no more than 20 or 30 concurrent users before you see performance degradation. While this was true with Access 2.0, it hasn’t been true since Access 97 was released more than twenty years ago.
Several users have successfully tested 200+ concurrent users with little to no degradation. However, what these users are doing does matter. If all 200 users are running queries and reports with data updates, you might see some degradation. From a technology perspective, such a limit would apply to any load-based operation, but even a Jet database can be optimized to run at such loads.
For even more users, the backend database can be migrated to SQL Server or SQL Azure.
