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.

Developer ribbon in Excel 2007

Give the macro a name and click ‘Create’.

Creating a macro in Excel 2007

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”.

Add a reference in Excel to the Microsoft ActiveX Data Object 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.

Running a macro in Excel 2007

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

Error you will get if there is no reference to the Microsoft ActiveX Data Objects 2.8 Library

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

Data pulled from the AdventureWorks database with an Excel macro

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.

Running a stored procedure that accepts parameters with VBA

You will be asked to enter a ProductId.

Provide an integer value for the procedure

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

image

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.


3 Comments

  1. Simon says:

    Thanks. Very useful information!

  2. Sean says:

    I am trying to extend this for more than one parameter and am receiving the following error:

    Syntax error or access violation

    With Cmd
    .CommandType = adCmdStoredProc
    .CommandText = “pa.pr_Sample ” & Cstr(p1) & “, ” & cstr(p2) & “, ” & _
    CStr(p3) & “, ” & CStr(p4) & “, ” & CStr(p5) & “, ” & CStr(p6) & “, ” & CStr(p7)
    End With

  3. Rhys says:

    Hi Sean,

    I don’t have access to a Windows PC now but I’d print out the command in a MsgBox. Something like MsgBox(“pa.pr_Sample ” & Cstr(p1) & “, ” & cstr(p2) & “, ” & _
    CStr(p3) & “, ” & CStr(p4) & “, ” & CStr(p5) & “, ” & CStr(p6) & “, ” & CStr(p7))

    If you print out the text you’ll probably be able to spot the sql syntax error.

    Cheers

    Rhys

Leave a Reply