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.

image thumb13 Practical VBA Examples for the DBA: Part 1

Give the macro a name and click ‘Create’.

image thumb14 Practical VBA Examples for the DBA: Part 1

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

image thumb15 Practical VBA Examples for the DBA: Part 1

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

image thumb16 Practical VBA Examples for the DBA: Part 1

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

image thumb17 Practical VBA Examples for the DBA: Part 1

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

image thumb18 Practical VBA Examples for the DBA: Part 1

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.

image thumb19 Practical VBA Examples for the DBA: Part 1

You will be asked to enter a ProductId.

image thumb20 Practical VBA Examples for the DBA: Part 1

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

image thumb21 Practical VBA Examples for the DBA: Part 1

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

Current ye@r *