Dedicated database application in MS Access VBA is definitely one of the solutions most often chosen by customers. It is also often preferred solution by the client's IT department due to the fact that the Office environments is already installed on their workstations. We have a very flexible data import / export mechanism and an excellent reporting module at our disposal. Important when choosing a program in Access VBA is also the ability to run the application on the free Access Runtime and good cooperation with MS SQL Server, Excel and MS Office applications.
Most of Excel users, especially at the managerial level, have workbooks full of data and macros that they have developed over the years, which play a very important role in the process of consolidation and analysis of information, and, consequently, in the process of company management. We often deal here with many related Excel files, where if even one of them is deleted, the result of the final calculation may be distorted. It is difficult to identify such a source of errors in the calculations, because we often have to read the content of each connected file. It happens that a set of Excel files is sometimes shared on a network drive, and the data from the files are linked to a workbook that serves as our "main application" on workstations. A set of Excel files is often a sufficient solution. However, it can also be prone to data corruption, data inconsistency, inefficient and cumbersome to publish and manage.
Many of you are surely wondering about the possibility of transferring your Excel "prototype" of the program from Excel files to a dedicated database application. Due to the great mechanisms of data import, the speed of preparation of the initial version of the program and the lack of the need to install additional software on the workstations, the Access database often becomes a natural choice here. Continuous support for Access and Office by the manufacturer is also important (subsequent versions of Access and Access Runtime and their update packages are released).
A professionally prepared database application in Access looks and behaves just like a standalone Windows program. It has its own menu (the Access menu visible in the design process will be hidden), forms, optimized queries, aesthetically prepared reports-printouts adapted to a specific paper format. The custom-made solution should be prepared to work on both the full version of Access and the free Access Runtime (no Access required).
In case of my solutions the final version is almost always divided into two Access files: front-end (application) and back-end (data).
I encourage you to read the descriptions of several examples of Access database application projects that I had the pleasure to implement. Sample projects.
(PL version) Samodzielne aplikacje na bazie programu MS Access