ADO Example Importing SQL Data

For our first ADO example, we're going to use the AdventureWorks sample database provided by Microsoft. You can install a copy of the AdventureWorks database by running the file AdventureWorksDB.msi.

You will be using SQL Server 2005 Management Studio Express to view the various database objects. To install Management Studio Express, run S0LServer2005_SSMSEE.msi.

1. Open a new workbook and name it DataAccessSample04.xlsm.

2. Before we begin using ADO in Excel 2007, we must add a reference to the ADO 2.8 library (see Figure 2-21).

a. In the VBE, choose Tools > References.

b. Select the Microsoft ActiveX Data Objects 2.8 library.

References - VBAProject

Available References:

0 Visual Basic For Applications 0 Microsoft Excel 12.0 Object Library 0 OLE Automation

0 Microsoft Office 12.0 Object Library 0 Microsoft ActiveX Data Objects 2.3 Library ✓ Microsoft DAO 3.6 Object Library

□ IAS Helper COM Component 1.0 Type Library

□ IAS RADIUS Protocol 1.0 Type Library

□ Acrobat Access 2.0 Type Library

□ Acrobat Access 3.0 Type Library

□ AcroIEHelper 1.0 Type Library

□ Active DS Type Library

□ Active Setup Control Library n ArHwpMnvip rnntrnl tvnp lihrarv

Priority ±1

Help

Microsoft DAO 3.6 Object Library

Location: C: program Files^Common Files Microsoft Shared^ AO\dao 36C Language: Standard

Figure 2-21. Adding a reference to the ADO 2.8 library

If you have SQL Server 2005 installed on your machine, you can use that instead of SQL Server 2005 Management Studio Express.

For our first example, we'll be using a parameterized stored procedure to return a list of Adventure Works employees for a selected manager. We'll enter the manager's employee ID and retrieve a list of that manager's direct and indirect reports.

The AdventureWorks database contains a stored procedure called uspGetManagerEmployees. If we expand that item in the Stored Procedures tree, we see that it takes one parameter, ManagerID, which is of the Integer data type (as shown in Figure 2-22).

□ Q Programmability □ CJ Stored Procedures

B □ System Stored Procedures ID H dbo.uspGetBillOf Materials B 0 dbo.uspGetEmployeeManagers B [2 dbo.uspGetManagerEmployees E) Cj Parameters

@ManagerID (int, Input, No default) ^ Returns integer E H dbo.uspGetWhereUsedProductID B H dbo.uspLogError B H dbo.uspPrintError

B H HumanResources.uspUpdateEmployeeHirelnfo B H HumanResources.uspUpdateEmployeeLogin B ¡3 HumanResources.uspUpdateEmployeePersonallnfo

Figure 2-22. Parameterized stored procedure in AdventureWorks database, as viewed in SQL Server 2005 Management Studio Express

1. In the VBE, add a standard module.

2. Create a new subroutine called GetManagerEmployeeListSQL.

3. Add the following variable declarations:

Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim param As ADODB.Parameter Dim xlSheet As Worksheet Dim rs As ADODB.Recordset Dim sConnString As String Dim i As Integer

We're using a few ADO objects to retrieve our data: an ADO Connection object to connect to the data, an ADO Command object to run our stored procedure, an ADO Parameter object to pass the ManagerID data to the stored procedure, and an ADO Recordset object to hold the results of our stored procedure.

In this example we are going to use cell A1 to hold the ManagerID information for our stored procedure's parameter. Let's add a modified version of the code we've been using to set up and clear our Excel worksheet:

Set xlSheet = Sheets("Sheet1")

xlSheet.Activate

Range("A3").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "Al").Select

Although this looks very similar to the code used in the DAO examples, the third line, Range("A3").Activate, has changed. The DAO examples activated cell A1 to clear the entire current region on the worksheet. Since we're using cell A1 as input to our stored procedure in this example, we want to start clearing the contiguous range beginning at cell A3 instead. Let's open our connection and assign it to a Command object:

Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;"

& "Database=AdventureWorks;Trusted_Connection=yes;" cnn.Open sConnString

Set cmd = New ADODB.Command cmd.ActiveConnection = cnn

Note To connect to a named instance of SQL Server, the convention is to use a server name of the format <servername>\<instancename>. Note the way the Server property is set in our previous example: Server=MyServerName\SOLEXPRESS.

Now let's create our Parameter object, fill some of its properties, and add it to our Command object.

Set param = New ADODB.Parameter

With param

.Name = "ManagerID" .Type = adInteger

.Value = ActiveSheet.Range("A1").Value

End With

With cmd

.CommandType = adCmdStoredProc .CommandText = "uspGetManagerEmployees" .Parameters.Append param

End With

We are setting the Parameter object's Name property to ManagerID, as called for by the stored procedure, and telling it to use the Integer data type. Finally, we set its Value property to whatever value is contained in the active sheet's cell A1.

Once that's done, we set up our Command object by telling it what kind of command we need (stored procedure), and the name of the stored procedure. Then we append our Parameter object to the Command object's Parameters collection.

Table 2-4 gives a list of ADO data type enums, along with their actual values and the corresponding Access and SQL data types they refer to.

Table 2-4. ADO Data Types

Data Type

Value

Access

SQL Server

adBigInt

20

BigInt (SQL Server 2000 +)

adBinary

128

Binary TimeStamp

adBoolean

11

YesNo

Bit

adChar

129

Char

adCurrency

6

Currency

Money SmallMoney

adDate

7

Date

DateTime

adDBTimeStamp

135

DateTime (Access 97 [ODBC])

DateTime SmallDateTime

adDecimal

14

adDouble

5

Double

Float

adGUID

72

ReplicationID (Access 97 [OLEDB]), (Access 2000 [OLEDB])

UniqueIdentifier (SQL Server 7.0 +)

adIDispatch

9

Continued

Data Type

Value

Access

SQL Server

adInteger

Integer

Long

Identity (SQL Server 6.5) Int

adLongVarBinary

205

OLEObject

Image

adLongVarChar

201

Memo (Access 97) Hyperlink (Access 97)

Text

adLongVarWChar

203

Memo (Access 2000 [OLEDB]) Hyperlink(Access 2000 [OLEDB])

NText (SQL Server 7.0 +)

adNumeric

131

Decimal (Access 2000 [OLEDB])

Decimal Numeric

adSingle

4

Single

Real

adSmallInt

2

Integer

SmallInt

adUnsignedTinyInt

17

Byte

TinyInt

adVarBinary

204

ReplicationID (Access 97)

VarBinary

adVarChar

200

Text (Access 97)

VarChar

adVariant

12

Sql Variant (SQL Server 2000 +)

adVarWChar

202

Text (Access 2000 [OLEDB])

NVarChar (SQL Server 7.0 +)

adWChar

130

NChar (SQL Server 7.0 +)

The remainder of our code is basically identical to our previous examples. Listing 2-5 shows what the finished subroutine looks like.

Listing 2-5. Calling Parameterized SQL in VBA

Sub GetManagerEmployeeListSQL() Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim param As ADODB.Parameter Dim xlSheet As Worksheet Dim rs As ADODB.Recordset Dim sConnString As String Dim i As Integer

Set xlSheet = Sheets("Sheet1")

xlSheet.Activate

Range("A3").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range(

Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;"

& "Database=AdventureWorks;Trusted_Connection=yes;" cnn.Open sConnString

Set cmd = New ADODB.Command cmd.ActiveConnection = cnn

Set param = New ADODB.Parameter With param

.Name = "ManagerID" .Type = adInteger

.Value = ActiveSheet.Range("A1").Value End With

With cmd

.CommandType = adCmdStoredProc .CommandText = "uspGetManagerEmployees" .Parameters.Append param End With

Set rs = New ADODB.Recordset Set rs = cmd.Execute

For i = 1 To rs.Fields.Count

ActiveSheet.Cells(3, i).Value = rs.Fields(i - 1).Name Next i xlSheet.Range(xlSheet.Cells(3, 1), _

xlSheet.Cells(3, rs.Fields.Count)).Font.Bold = True

ActiveSheet.Range("A4").CopyFromRecordset rs xlSheet.Select Range( "A3").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "A1").Select rs.Close cnn.Close

Set cmd = Nothing Set param = Nothing Set rs = Nothing Set cnn = Nothing Set xlSheet = Nothing

End Sub

Note that our cleanup code also refers to cell A3 when setting up the worksheet with the AutoFit method.

We can test this code out by entering a ManagerID in cell A1 on Sheet1 and running the GetManagerEmployeeListSQL method from the macro list.

2. Choose GetManagerEmployeeListSQL from the macro list and run the code. The results are shown in Figure 2-23.

Country Abbreviations
Figure 2-23. Result of GetManagerEmployeeListSQL code

3. Enter a manager ID of 21 in cell A1 and run the code again. You'll see a longer list of employees since this is a higher-level manager.

4. Enter a manager ID of 16 again to see the setup code at work, clearing the used cells for the next round of data import.

EXCEL 97 AND ADO RECORDSETS

For efficiency and performance, CopyFromRecordset is the preferred method of filling cells with data from an ADO recordset. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:

Run-time error 430:

Class does not support Automation or does not support expected interface.

In the code sample, you can avoid this error by checking Excel's version using the ExcelVersionShort property from the cExcelUtils class in the codeLib.xlsm workbook included on the CD so that you do not use CopyFromRecordset for the 97 version.

Property Get ExcelVersionShort() As String

Dim xlApp As Object

Dim sExcelVersionShort As String

Set xlApp = CreateObject("Excel.Application")

sExcelVersionShort = Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1) Set xlApp = Nothing

ExcelVersionShort = sExcelVersionShort End Property

Property Get ExcelVersion() As String

Dim xlApp As Object

Dim sExcelVersion As String

Set xlApp = CreateObject("Excel.Application") sExcelVersion = xlApp.Version

Set xlApp = Nothing ExcelVersion = sExcelVersion End Property

If Excel 97 is detected, use the GetRows method of the ADO recordset to copy the recordset into an array. If you assign the array returned by GetRows to a range of cells in the worksheet, the data goes across the columns instead of down the rows. For example, if the recordset has two fields and ten rows, the array appears as two rows and ten columns. Therefore, you need to transpose the array using your TransposeDim() function before assigning the array to the range of cells.

+2 -1

Post a comment