In the case that we require the possibility of simultaneous work on the program by more than 5 users or the amount of data stored in the Access file can grow quickly, I recommend using a SQL server as a data source, e.g. free MS SQL Server Express. The main application (front-end) is then still written in Access VBA, however, queries and database handling mechanisms will be optimized in it to work with the SQL server database. A large part of the data processing and business logic's is programmed on the side of the SQL database itself, e.g. using Access pass-through queries and server stored procedures. This solution significantly increases the security, ease of making back-up, the possibility of scaling the project in the case of a larger than originally assumed number of users and data (for instance we can migrate to a "higher" version of the SQL server), without the need to interfere with the written application's code. What is more, many customers often have a commercial version of the software SQL server, which we can also use then.
The second, extremely important advantage of connecting MS Access application to a SQL database, which cannot be overestimated, and which I have used many times, is the ability to iterchange data from the SQL database by applications compiled in the .NET Framework, both desktop and www. At the customer's request, I even converted the original Access solution into an online application in ASP.NET, where I could use the previously used SQL database.
Access connected with the SQL database is definitely reliable solution I prefer and recommend for more extensive, multi-station systems for production control, sales analysis, warehouse programs, etc.
I encourage you to familiarize yourself with my sample projects in which the above solution was used.
(PL version) Interfejs w postaci programu Access VBA w połączeniu z bazą SQL Server
