Enforcing database rules with VBA and SQL Server code
On Enforcing Database Constraints Using MS VBA Event Driven Procedures and SQL Server
Databases
Summary
Many software programs need to keep their data accurate by following strict rules called constraints. This paper explains how to use Microsoft VBA code inside applications to automatically enforce those data rules when working with SQL Server databases. The authors provide a clear step-by-step method and example code from a family tree database to show how this enforcement works. They also demonstrate that their approach can be adapted easily for other database systems, including some that aren’t strictly relational.
What this means in practice
- •For database application developers: Use the provided algorithm and VBA coding examples to enforce complex data constraints reliably in applications built on SQL Server.
- •For enterprise software integrators: Adapt the methodology with minimal changes to ensure data quality when integrating other SQL or NoSQL backend systems with similar frontends.
Authors
Diana Christina Mancas
Abstract
The goal of this paper is to provide a rigorous methodology for enforcing database constraints using MS VBA event-driven procedures for software applications built on top of MS SQL Server databases, in the framework of the software engineering Database Constraint-Driven Design and Development. The original contribution is a pseudo-code algorithm for assisting developers in this process. We exemplify the results of using it with some VBA code examples taken from a genealogy database software application designed and developed using this methodology. Consequently, it enforces all the database constraints -be they relational or not- which govern this sub-universe of discourse, thus guaranteeing the highest possible quality of its managed data. We also show that the proposed algorithm works fine not only for other relational database management systems, but even with NoSQL platform backends, and that the modifications needed to adapt it to other similar SQL embedding frontend platforms are minimal.