Preparing a dedicated database application on request in Excel requires, first of all, ordering and validating data at the stage of entering them by end users. We usually want to write the data to an ordered structure - a table that we can later search using query builders, Excel functions, VBA code, ADO / DAO libraries and SQL language.

When we want to prepare windows with fields which controls allowed data type, we can of course use the data checking mechanisms built into the sheet (menu items available on the Data / Data tools / Data correctness ribbon). However, due to the need to secure the program from the side of its modification by the end user, an interface in the form of forms programmed in VBA may be a better solution. Such a solution allows us to completely hide data sheets and fully support the input, reading and presentation of data only through prepared windows. The executed program will not even resemble an Excel workbook, but an independent database application with its own interface (the Excel menu will be hidden), built-in lists, reports, analyzes and printouts. The greatest advantage of this approach will also be the possibility to "transfer" the program database itself in the future, either to an Access database file or even to a SQL server, and allow several users to work on a larger amount of data at the same time. It is important that by modifying the VBA code later, we can leave the application interface, which is often built for many months, and to which users are already accustomed, unchanged.

It is also possible to create an Excel VBA application, the data of which will be stored in a separate file, e.g. in the Access database format. Such a solution significantly speeds up the analysis and generation of summaries, makes the program resistant to data corruption and enables data sharing by many users in a much more effective way than simply sharing a workbook. In addition, launching such a project does not require the installation of any additional elements or libraries at the client's stand - it uses the resources of Excel, Office and Windows.






(PL version) Aplikacje - bazy danych w Excel VBA