Using the Data Access Layer

In my previous post , I described how to create a Data Access Layer in VBA for SQL Server. In this post, I am going to share some examples of how to actually use the layer to execute database tasks. For the examples, I created a simple database with one table called Country which I populated with a list of countries and their respective regions:

One thing to note beforehand, is that all of the VBA examples below are standalone examples. In each, I am declaring a reference to the database class. In a real world context, I would only declare this once publicly, and then access this reference whenever I need to execute a database task.

Getting Information from SQL Server

So the first task, is to retrieve data from the database and make it available in an ADO Recordset. Below is the VBA code to do this using the layer classes. It runs a stored proc called spGetItems, and then outputs the results to the VBE Intermediate Window:

And this is the code for the stored procedure spGetItems:

The stored procedure spGetItems also accepts a variable called region which allows for the results to be filtered. If I wanted to only return all the countries in Asia, then I would adapt the code above to look like so:

Running a command in SQL Server

The following code is for a stored procedure called spDeleteItems, which deletes a country from the table Country:

It has three parameters, a single input parameter (name) and two output parameters (rowcount, err). The first is the value of the country I want to delete which gets sent to the stored procedure from the VBA code. The other parameters are variables that get populated by SQL Server, which I want to receive back from the database. They represent the number of rows affected, and also whether or not SQL Server generated any error whilst trying to carry out the delete operation. This is how to use the layer class to run this stored procedure which sends and retrieves values to and from the stored procedure:

The VBA code sends the value “France” to the stored procedure as well as two empty variables. It then executes the query server side. After execution, the stored procedure then populates the two empty variables with values, and sends them back to the client side VBA code. The rather nice thing about this setup is that it provides three indicators to help debug issues. These are:

  • The class method returns a Boolean value to indicate whether the method ran successfully or not.
  • The class returns a string with any Client side ADO errors that occurred when executing the stored proc.
  • The class makes available any errors it is sent from the database as an output parameter.

The VBA code for adding and updating data would be no different from the code for deleting, since the VBA code effectively does the same thing each time, i.e. it runs a stored procedure and returns the values of any output values to the VBA code.

Conclusions

I hope you can see from the examples, that accessing SQL Server with VBA using the layer classes has been greatly simplified. I did not have to worry about connection details or about which set of ADO commands I needed to run my stored procedures. The class provided two basic methods, one to specify parameters and the other to identify the query I wanted to run, it then did the rest of the work. In a project, I would now be free to just focus on writing the business logic of my application.

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

Share :Facebooktwittergoogle_plusredditpinterestmail

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.