ADO Interface with CCA client-server products

ActiveX Data Object and Microsoft Excel

By Steve Nelson

This article examines the use of ADO with Excel. We will show how to access Model 204 data via Connect* and System 1032 data via System 1032 ODBC Driver.

We will pay close attention to the Visual Basic Editor, which is provided with Excel. Once you are familiar with the Editor, you can create a simple application that populates a worksheet.

Using Visual Basic code

The Visual Basic code in this article accesses data using the ADO Interface and populates cells in a worksheet. This coding approach may differ from yours or you may choose to write your own code. In either case, you can simply cut and paste the example, change the data source information and provide an easy access example for demonstration purposes or for use at your site. Our objective is to show at least one way to use CCA's database client/server products.

Invoking the Visual Basic Editor

To begin, you need the client/server or middleware for your database. The code we provide works with either the System1032 ODBC Driver or the Connect* ODBC driver. Each driver accesses a different table, but the access methods through ADO are the same.

1. At your PC select the Excel icon and create a new worksheet.

2. To start the Visual Basic Editor shown in Figure 1, select Tools Macro Visual Basic Editor.

From the many Visual Basic Editor features, we focussed on inserting a module that contains a subroutine to access data and populate cells within the worksheet. Our example could be used in conjunction with other parts of an application within Excel.

Figure 1. Starting the Visual Basic Editor dialog box

3. To create a separate code dialog box as shown in Figure 2, select Insert Module. Selecting this feature creates a separate Code window for code data entry.

Figure 2. Visual Basic Code dialog box

Our example uses the demonstration data for Model 204 data.

Creating the ADOConnect subroutine

Using the code shown in Figure 3 create a subroutine called ADOConnect. You can insert this same code into your new module. For System 1032 change Open method to reflect your ODBC data source name for the appropriate database. Uncomment or re-comment the statements for your database.

Figure 3.Code for ADOConnect subroutine

Sub ADOConnect 'Dim vars Dim rcConnect Dim rsResultSet Dim counter ' Cell Counter   'Set new connect Set rcConnect = CreateObject("adodb.connection") Set rsResultSet = CreateObject("adodb.recordset")   'Establish a connect rcConnect.Open ("DSN=Model204;uid=<user>;pwd=<pass>;") _ Model 204 ë rcConnect.Open ("DSN=S1032_IVP;uid=<user>;pwd=<pass>;") _ System 1032   ' Get Data Set rsResultSet.ActiveConnection = rcConnect rsResultSet.Open "Select * from DEMO.CLAIMS89" _ Model 204 ërsResultSet.Open "Select * from EMPLOY" _ System 1032   ëPut into Excel Sheet For counter = 1 To 20 ëInsert Model 204 SQL data into a worksheet Worksheets("Sheet1").Cells(counter, 1).Value=rsResultSet("POLICY_NO").Value Worksheets("Sheet1").Cells(counter, 2).Value=rsResultSet("CLAIM_NO").Value Worksheets("Sheet1").Cells(counter, 3).Value=rsResultSet("CLAIMEE").Value Worksheets("Sheet1").Cells(counter, 4).Value=rsResultSet("LOCATION").Value Worksheets("Sheet1").Cells(counter, 5).Value=rsResultSet("WEATHER").Value   ë Insert System 1032 SQL data into a worksheet ëWorksheets("Sheet1").Cells(counter, 1).Value=rsResultSet("EMPLOYEE_NUMBER").Value ëWorksheets("Sheet1").Cells(counter, 2).Value=rsResultSet("LAST_NAME").Value ëWorksheets("Sheet1").Cells(counter, 3).Value=rsResultSet("FIRST_NAME").Value ëWorksheets("Sheet1").Cells(counter, 4).Value=rsResultSet("DEPARTMENT").Value ëWorksheets("Sheet1").Cells(counter, 5).Value=rsResultSet("MANAGER").Value rsResultSet.MoveNext Next counter  'Close Connect and Resultset rsResultSet.Close rcConnect.Close   Set rsResultSet = Nothing Set rcConnect = Nothing   End Sub ë End subroutine ADOConnect

How ADOConnect code works

In the subroutine code, we defined a Connection object called rcConnect and a Recordset called rsResultSet. Each has its own set of Properties, Methods and Events. These objects enable us to select some rows and field names from the database, and place each field into a cell on an existing worksheet. After we populate the worksheet, we close all objects and cleanup our environment.

Once you have completed the coding, save the module. You can rename your module using the Properties option. Press F4 for the Properties View. You must keep track of the name, if you change it. Our example module is called ADOConnect, shown in Figure 4. We will next call this module weíve created from the worksheet.

Figure 4. Selecting the ADOConnect subroutine

Calling the ADOConnect subroutine

Close the Visual Basic Editor and return to Excel. To access the ADOConnect module from the worksheet, select Tools Macro Macros. Figure 5 displays your list of available macros.

Figure 5. Selecting a module in the Macro dialog box

A

Select the module that you created, and press Run. The Model 204 output is shown in Figure 6.

Figure 6. Model 204 data file displayed in Excel

Now you can manipulate your data any way you like.

Coming attractions

In Part 4 of the ADO Interface Series we will discuss how to interface Model 204 and System 1032 ODBC Driver with Microsoft Word.

If you would like more information about ADO, the web site address is:http://www.microsoft.com/data/ado

Copyright © 2008 Computer Corporation of America.
All right reserved. Published in the United States of America.

Home DownloadsownFeedback

Contact CCA Webmaster
Copyright 2008