In this post, I thought I would share some ideas about creating a Data Access Layer in VBA for SQL Server. The idea behind creating this layer is to organize all the low level coding tasks associated with accessing a SQL Server database into one area in an application. Typically, these tasks involve writing methods using  ADO (Active Data Objects) to either fetch  data or process some database command. The idea behind the layer is to encapsulate these methods, and then make them available via the layer to other parts of the application that need to carry out database tasks.

Objectives

There are a number of reasons for organizing code in this way:

• It simplifies the code involved with interacting with a database.
• It leads to a clear separation between the low level database tasks and the main application code.
• It allows for client side database access to be tested independently of any user interface.
• It standardizes access to the database and ensures consistency across the application.
• The layer is portable and can be reused in other applications.
• It avoids code repetition, by allowing for generic methods that can carry out tasks.
• The database implementation can be changed without impacting code outside of the layer.
• It allows for server credentials to be stored centrally in an application.

The Design

There are many different ways you can implement a Data Access Layer in VBA, in my case I have opted for the following design:

Database Tasks

In the design, the main class is called clsDatabase, and it has two main methods:

• GetRecordsetFromStoredProc
• ExecuteStoredProc

The first method is to used to retrieve data from the database and the second is used to execute SQL Server tasks wrapped in stored procedures. With ADO, it is possible to send SQL statements directly to a database to query its raw tables. However it is best to avoid this, and actually prevent its possibility. This is because it can lead to security vulnerabilities and allow for the possibility of a SQL injection attack. Additionally, when it comes to performance, database operations are always better handled server side rather than client side. This class ensures that the only communication between the application client and the database occurs via stored procedures. The class also has methods that allow for parameters to be added to any stored procedure that requires them.

Encapsulation

A key feature of the clsDatabase class, is that it operates as a black box. It should not expose ADO tasks or methods to any other area of code. All of these need to be enclosed within the layer. The one exception to this, will be that the class will have to return data as an ADO Recordset to outside calling code. This is because some VBA methods require a Recordset object as an argument in order to work. The most notable example of this is the CopyFromRecordSet method of the Range object in Excel.  I could expand the class to handle this Range method and incorporate outputting the data to a worksheet. However, this would tie the layer down to a specific platform i.e. Excel. The layer should be independent of this, so it can be used in other Office Applications like Microsoft Access. So apart from the method that gets data, the other methods in my class either return standard variable types such as a Boolean value to indicate whether a task completed, or a string message to indicate an error.

Handling Multiple Databases

In the design, I have decoupled connection information from the main class, and created another class for this called clsDBCredentals. This class is used to specify the server details and database name as well as how to connect to SQL Server. The main class clsDatabase simply needs an instance of the clsDBCredentials class to perform its tasks. The reason for this separation is because I wanted to allow for the design of the layer to be flexible enough to cope with connecting to multiple databases. This scenario can come up if I am working with a production database that also has a development and UAT environment.

The Layer’s Factory

The purpose of the clsDBInstance class is to handle the responsibility of creating objects using the clsDatabase and clsDBCredential classes. It simplifies the process of having  two classes. It is here that I specify the details of multiple databases. This allows for my main classes to be kept closed for modification, a key characteristic of encapsulation in object-orientated design. The class also offers different ways it can create an instance of the clsDatabase class, adding greater flexibility to this approach.

Singleton Design Pattern

With the GetSharedDatabase method, I use a Singleton design pattern to offer the possibility of reusing an open instance of the database class within the layer. Repeatedly opening and closing a database can be a wasteful process. The shared connection method creates a single instance of the database object and reuses it for other tasks if it is open and available.

Implementing the Design in VBA

In order to use the following code, the following references need to be checked in the VBA Reference Window:

The code requires the creation of three VBA classes called clsDatabase, clsDBInstance and clsDBCredentials.

The following is the code for the main class clsDatabase:

Note: In my class, I have a private method called GetInformationError which is based upon this post from devX.com.

This is the code for the class csDBInstance:

The name of the server and database need to be changed to whatever database is being worked upon. The code above is setup to use trusted security. However, if a login and password is required then the trusted security attribute needs to be false, and the login and password need to be added like so:

Finally, this is the code for the class csDBCredentials:

In the next post, I will show some examples of how the above classes are used in a real world context.

Thank you for reading this post. Please take time to read the disclaimer about content found on this site.

Share :