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

2 thoughts on “Using the Data Access Layer”

  1. Like you, I prefer to use stored procedures and parameters. However, sometimes I have to use straight up a string SQL command that I built. Do you have a method written for this class to do this?

    1. Hi, Thanks for the comment. There is not method in the class for that. However, you can add the following code to the class which will do what you require. It takes in a SQL statement in the form of a string and returns an ADO recordset. You can call the method and manipulate the output in the same way as the examples above. NB: I haven’t had chance to fully test it, but it should in theory work.

      Public Function GetDataFromSQLStatement(ByVal strSQL As String) As ADODB.Recordset

      On Error GoTo Error_Handler

      Dim oRst As New ADODB.Recordset
      Set GetDataFromSQLStatement = Nothing
      If Not IsConnected Then
      GoTo Exit_Here
      End If
      oRst.Open strSQL, moConn, adOpenKeyset, adLockOptimistic
      Set GetDataFromSQLStatement = oRst

      EXIT_HERE:
      Set oRst = Nothing
      Exit Function
      ERROR_HANDLER:
      mstrADOErrors = GetADOErrorInformation()
      mstrADOErrors = mstrADOErrors & vbCrLf & “Err Description:” & Err.Description
      GoTo EXIT_HERE
      End Function

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

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