Microsoft Access is a powerful database platform, and perfect for many small and mid-size business applications.  However, it does have some limitations.  Below is a list of five things to consider before investing in a Microsoft Access system.

There is a learning curve (and/or cost) to build a complex Microsoft Access database.

If this is your first Microsoft Access database system, you will need to spend some time learning how to build forms and reports in Microsoft Access.  The wizards provided in the platform create simple queries, forms, and reports.  If you need something more complex (and there is the ability to make very complex objects), and you would like to do the project yourself, Extra Mile Data can provide the coaching that you need.  Or if you would prefer to hand it off to an expert, Extra Mile Data also does complete database design and development, start to finish.

Huge amounts of data, complex queries with lots of tables, or a poor network, can slow down forms and reports.

A Microsoft Access database system is meant to include two files: the front-end (the data entry forms, the reports, and the logic) and the back-end (the data tables and relationships).  The front-end file is installed on each user’s computer, and the back-end file is located in a shared network folder.  The back-end tables are linked to the front-end.

When a query is run, all the data necessary to perform the query is sent across the network to the front-end to be processed.  This means that queries that include a lot of tables must pull data from all of the tables down to the user’s computer before the results are acquired.  If the tables have a lot of data, this increases the amount of time it takes to get results for the user.  Forms and reports will run slower.

If a Microsoft Access file is used for the front-end and a Microsoft SQL Server database is used for the back-end, you can have a true client-server environment.  The query request can be processed inside SQL Server, and just the results are passed back to the front-end.  This can dramatically increase performance.  Microsoft provides a tool to migrate tables and data from Microsoft Access to Microsoft SQL Server, but Microsoft SQL Server may require specialized hardware and software, which would increase the development cost of the system.  We generally don’t recommend this path unless performance must be increased.

There is a limit to the number of concurrent users.

According to Microsoft, the maximum number of concurrent users is 255.  It may be possible for that many users to touch the data at one time, but performance will be much better at a lower number of users.  A limit of 20 may be more realistic for a high-performance system.

Using Microsoft Access as the front-end and Microsoft SQL Server as the back-end can increase the allowed number of concurrent users for your system.

Microsoft Access is not designed to be used on the internet.

The Microsoft Access database platform is designed to create Windows desktop applications.  A Microsoft Access database system is perfect for an environment where the back-end database file is located in a shared network folder, and the front-end file is installed on each user’s computer, often on the Windows Desktop.

If you need to use the program remotely, the most straightforward option is to use Windows Remote Desktop, or use an internet-based remote support tool, to connect to the user’s computer.  This solution requires that the computer is on, the user has an active internet or VPN connection, and that the computer is not being used by someone else at the same time.  A more sophisticated solution would be to provide virtual user desktops that are hosted on a remote server.  This would require a server with hardware that can handle multiple users at once.

Microsoft Access does not have built-in user-level security.

Microsoft Access does offer database encryption, if you would like to use it, including a database password.  However, if you wish to limit the features (forms, reports, etc.) that a particular user has permissions for, you will need to add those limitations yourself using VBA (Visual Basic for Applications – the programming language used by Microsoft Access).  Extra Mile Data can help you with that.

If you would like to explore how Microsoft Access can work for your business, give us a call at (505) 369-1650.

Some additional articles about Microsoft Access:
When to use a Microsoft Access Database System
Over 100 Examples of How Businesses use Microsoft Access – Part 1