In the case that we require the possibility of simultaneous work by more users or the estimated amount of data can grow quickly, I recommend using a SQL server as a data storage. The main application (front-end) can be still written in Access VBA, however, queries and database handling mechanisms will be optimized to work with the SQL server engine. A large part of the data processing and business logic's is programmed on with 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 "bigger" option 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 interchange 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
