Practical VBA Examples for the DBA: Part 1

I’ve never been a huge fan of VBA but it can be very useful for quickly providing interfaces to your databases. The examples here use Macros in Excel 2007 to execute stored procedures on SQL Server and provide data back to the user. These examples use the AdventureWorks sample database.

Execute a Stored Procedure with VBA

This example will execute a stored procedure that returns a resultset. The data returned will be copied to the active worksheet. First create the below stored procedure in your AdventureWorks database.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_test]
AS

	SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[MakeFlag]
      ,[FinishedGoodsFlag]
      ,[Color]
      ,[SafetyStockLevel]
      ,[ReorderPoint]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[SizeUnitMeasureCode]
      ,[WeightUnitMeasureCode]
      ,[Weight]
      ,[DaysToManufacture]
      ,[ProductLine]
      ,[Class]
      ,[Style]
      ,[ProductSubcategoryID]
      ,[ProductModelID]
      ,[SellStartDate]
      ,[SellEndDate]
      ,[DiscontinuedDate]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Production].[Product]

Open up a new workbook in Excel. Next you need to add a new macro. Click the “Macros” button on the developer ribbon. N.B. You may need to enable the Developer ribbon.

Give the macro a name and click ‘Create’.

You will then be taken to the VBA editor. Paste in the entire code below replacing what is already there. Note you will need to change the value for server in the connection string. The connection string also assumes you are using Windows Authentication.

Sub execute_proc()

    ' Setup connection string
    Dim connStr As String
    connStr = "driver={sql server};server=localhost\sql2005;"
    connStr = connStr & "Database=AdventureWorks;TrustedConnection=True;"

    ' Setup the connection to the database
    Dim connection As ADODB.connection
    Set connection = New ADODB.connection
    connection.connectionString = connStr
    ' Open the connection
    connection.Open

    ' Open recordset.
    Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = connection
    Cmd1.CommandText = "usp_test"
    Cmd1.CommandType = adCmdStoredProc
    Set Results = Cmd1.Execute()

    ' Clear the data from the active worksheet
    Cells.Select
    Cells.ClearContents

    ' Add column headers to the sheet
    headers = Results.Fields.Count
    For iCol = 1 To headers
        Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
    Next

    ' Copy the resultset to the active worksheet
    Cells(2, 1).CopyFromRecordset Results

End Sub

Click Tools > References and scroll down the list and check “Microsoft ActiveX Data Objects 2.8 Library”.

Click File > Close and Return to Microsoft Excel. Click the macro button on the developer ribbon. Click the run button to execute the macro.

If you receive the below error then you haven’t correctly added the reference mentioned above. Go back and re-add it.

If all has gone well the macro should pull some data from the AdventureWorks database into the current worksheet.

Execute a Stored Procedure with Parameters and VBA

This example is very similar to above except this time we will execute a stored procedure that accepts an integer parameter. This parameter will be provided by the user entering a value in a popup input box.  Create the below stored procedure in your AdventureWorks database.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_test2]
	@ProductId INTEGER
AS

	SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[MakeFlag]
      ,[FinishedGoodsFlag]
      ,[Color]
      ,[SafetyStockLevel]
      ,[ReorderPoint]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[SizeUnitMeasureCode]
      ,[WeightUnitMeasureCode]
      ,[Weight]
      ,[DaysToManufacture]
      ,[ProductLine]
      ,[Class]
      ,[Style]
      ,[ProductSubcategoryID]
      ,[ProductModelID]
      ,[SellStartDate]
      ,[SellEndDate]
      ,[DiscontinuedDate]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Production].[Product]
  WHERE [ProductID] = @ProductId;

Follow the same procedure above for creating a macro but add the below code. Ensure the value for server is changed in the connection string.

Sub usp_test2()

    Dim temp As String
    Dim ProductId As Integer

    Do
        temp = InputBox("Enter a ProductId")
        ' Bizarrely have to check for cancel!
        If StrPtr(strwholeNo) = False Then
            Exit Sub
        End If
    Loop Until IsNumeric(temp)

    'Convert the input to an integer
    ProductId = CInt(temp)

    ' Setup connection string
    Dim connStr As String
    connStr = "driver={sql server};server=localhost\sql2005;"
    connStr = connStr & "Database=AdventureWorks;TrustedConnection=True;"

    ' Setup the connection to the database
    Dim connection As ADODB.connection
    Set connection = New ADODB.connection
    connection.connectionString = connStr
    ' Open the connection
    connection.Open

    ' Open recordset.
    Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = connection
    Cmd1.CommandText = "usp_test2 " & CStr(ProductId)
    Set Results = Cmd1.Execute()

    ' Clear the data from the active worksheet
    Cells.Select
    Cells.ClearContents

    ' Add column headers to the sheet
    headers = Results.Fields.Count
    For iCol = 1 To headers
        Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
    Next

    ' Copy the resultset to the active worksheet
    Cells(2, 1).CopyFromRecordset Results

End Sub

A second macro called ‘usp_test2’ will appear in the Run macro dialog. Click run to execute it.

You will be asked to enter a ProductId.

Enter a 1 and click ‘OK’. The procedure should run and return one row of data.

In a future article I’ll be demonstrating the use of the VBA GUI editor to create some simple user interfaces to provide better interactivity with your data.