Data manager design | Contact Editor Link Search Menu Expand Document
Contact Editor

Overview

"Contact Editor" demos the Model-View-Presenter (MVP) pattern backed by persistent storage (MVP-DB) in VBA. I developed this mock data manager as a VBA OOP course project and an MVP-DB template/prototype for my VBA experiments.


Overview

Figure 1. Data management workflows.

The typical data management constituents are shown in Fig. 1. Usually, the user sends a query to the database, receives a response table, browses record data via a user form, and, possibly, updates the database. From the user perspective, the data management process involves three key players: the user, persistent storage, and GUI acting as an intermediary between the first two. Functionally, the actual intermediary is the data manager application responsible for presenting the GUI, maintaining a local data container, and transferring the data (user ⇔ GUI ⇔ data container ⇔ persistent storage), as shown in Fig. 2. The left part of the figure marked with green arrows ("user ⇔ GUI" and "GUI ⇔ data container" interactions) can be implemented via the MVP pattern, and the remaining "data container ⇔ persistent storage" interaction can be handled via a "storage" library.


Data Management Overview

Figure 2. Data manager application.

Development environment and repo structure

Primarily, I use Excel 2002 for development and also run tests on Excel 2016. Contact Editor demo is a VBA app and is part of an Excel Workbook Contact Editor.xls available from the root of this repo. Additionally, all code modules and user forms are available from the Project folder (which acts as a container and corresponds to the VBA project root within the .xls file). Rubber Duck VBA add-in greatly facilitated the development process, and the project structure is exported/imported using the RDVBA Project Utils VBA module. The repo includes a sample SQLite database used by one test module. This database is accessed via the "ADODB" backend and relies on the SQLite ODBC driver. While the tests should pass when the provided driver distribution is used, I compiled the driver myself, as briefly discussed here, to use an up-to-date SQLite library with all extensions enabled.

Running the demo with different backends

ContactEditorRunner.RunContactEditor is the main entry for the demo.
ContactEditorPresenter.InitializeModel performs basic backend configuration.
The DataTableBackEnd variable found in the entry point sub determines the type of the primary storage backend. It can take one of the following values:

  • Worksheet for an Excel Worksheet (demo database file, worksheets "Contacts" and "ContactBrowser"),
  • CSV for a text delimited file (demo database file), and
  • ADODB for a relational database (demo SQLite database file).
  • SecureADODB for a relational database with SecureADODB Library fork integrated (demo SQLite database file).

While the ADODB/SecureADODB backends can connect to both "CSV" and "Worksheet" databases, the dedicated backends should be more efficient and can also be used for verification purposes. Also, note that since the ultimate focus of this project is accessing SQLite databases via the ADODB library, the database type used with ADODB/SecureADODB backends is hardcoded as SQLite in ContactEditorPresenter.InitializeModel.

Acknowledgments

A special thanks goes to Mathieu Guindon, a co-founder of the Rubber Duck VBA project and his RDVBA blog. RDVBA blog post describing a possible approach to abstracting a Worksheet-based persistent storage and a demo file helped me jump-start with storage integration. I also followed the blog post regarding the best practices for UserForm handling and the SO answer (and the last comment to that answer) regarding the modeless user forms.