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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Public Sub GetListOfCountries() Dim rstData As New ADODB.Recordset Dim oDBInstance As New clsDBInstance Dim oDatabase As clsDatabase Set oDatabase = oDBInstance.GetSharedDatabase() Set rstData = oDatabase.GetRecordsetFromStoredProc("spGetItems") rstData.MoveFirst Do While Not rstData.EOF Debug.Print rstData.Fields("CountryName").Value & " " & rstData.Fields("CountryRegion").Value rstData.MoveNext Loop oDBInstance.CloseSharedDatabase Set rstData = Nothing Set oDBInstance = Nothing Set oDatabase = Nothing End Sub |
And this is the code for the stored procedure spGetItems:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE [dbo].[spGetItems] @region varchar(50) = null AS BEGIN SET NOCOUNT ON; IF @region is null SELECT CountryName, CountryRegion FROM dbo.Country; ELSE SELECT CountryName, CountryRegion FROM dbo.Country WHERE CountryRegion = @region; End GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Public Sub GetListOfCountriesInAsia() Dim rstData As New ADODB.Recordset Dim oDBInstance As New clsDBInstance Dim oDatabase As clsDatabase Dim strRegion As String Set oDatabase = oDBInstance.GetSharedDatabase() strRegion = "ASIA" oDatabase.AddToParamList "region", adChar, Len(strRegion), strRegion, adParamInput Set rstData = oDatabase.GetRecordsetFromStoredProc("spGetItems") rstData.MoveFirst Do While Not rstData.EOF Debug.Print rstData.Fields("CountryName").Value & " " & rstData.Fields("CountryRegion").Value rstData.MoveNext Loop oDBInstance.CloseSharedDatabase Set rstData = Nothing Set oDBInstance = Nothing Set oDatabase = Nothing End Sub |
Running a command in SQL Server
The following code is for a stored procedure called spDeleteItems, which deletes a country from the table Country:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE [dbo].[spDeleteItem] @name varchar(50), @rowcount int OUTPUT, @err int OUTPUT AS BEGIN DELETE FROM dbo.Country WHERE CountryName = @name SELECT @err=@@error, @rowcount = @@ROWCOUNT End GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Public Sub DeleteData() Dim oDatabase As clsDatabase Dim oDBInstance As New clsDBInstance Dim strCountry As String Set oDatabase = oDBInstance.GetSharedDatabase() strCountry = "France" oDatabase.AddToParamList "name", adChar, Len(strCountry), strCountry, adParamInput oDatabase.AddToParamList "retcount", adSmallInt, 2, 0, adParamOutput oDatabase.AddToParamList "err", adSmallInt, 2, 0, adParamOutput If oDatabase.ExecuteStoredProc("spDeleteItem") Then Debug.Print "success executed proc" Debug.Print "rows affected =" & oDatabase.GetParamValue("retcount") Debug.Print "error from sql server=" & oDatabase.GetParamValue("err") Else Debug.Print "failed to execute the stored procedure" End If oDBInstance.CloseSharedDatabase Set oDBInstance = Nothing Set oDatabase = Nothing End Sub |
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 :
