TECH TIP Accessing data through spreadsheets By Kriss Rant Recent support for Microsoft Open Database Connectivity (ODBC) in HP IMAGE/SQL and ALLBASE/SQL allows for database access from many third-party PC applications currently available on desktop. Two popular spreadsheet applications, Microsoft Excel 5.0 and Lotus 1-2-3 Release 5 both recently added support for ODBC. ODBC is a standard application programming interface that enables third-party Microsoft Windows applications and tools to access IMAGE/SQL, ALLBASE/SQL and other databases in a client/server environment using structured query language (SQL) as a standard for accessing data. Getting started Several components are required for implementing a client/server application. The architecture requires that PCs be networked over a LAN connection to the HP 3000 servers. On the "client side," PCs are required with Microsoft Windows 3.1 and MS DOS 5.0 or later versions. It also is necessary to have a network protocol product that supports TCP/IP Windows Sockets (WINSOCK) or SPX/IPX. WINSOCK is supported by many PC network vendors such as Microsoft, Novell, and WRQ. Support for the SPX/IPX protocol is provided by Novell Netware. The client also will need HP ODBC A F0.16 or later. It comes bundled with IMAGE/SQL and ALLBASE/SQL at no additional cost. On the "server" side, an HP 3000 (MPE/iX 4.0 or later) is required. HP ThinLan/iX is required for TCP/IP support. If SPX/IPX protocol is being used, Netware/iX is also required. In addition, the server must have either HP IMAGE/SQL B.F0.24 or later, or HP ALLBASE/SQL A.F0.67 or later. (ALLBASE/SQL is available on both the HP 3000 and HP 9000.) Using Microsoft Excel Rather than try to describe how HP client/server technology can be used with spreadsheet applications for business needs, we've set up a hypothetical example involving inventory control for a manufacturing company. To monitor stock levels, we will extract information out of a Parts IMAGE/SQL database using Microsoft Excel. The following steps outline the query process to extract the required information out of the database: o Before starting a query in Microsoft Excel 5.0, it is necessary to set up data sources. A data source describes the the database to which the user wants to connect; the server on which the database resides; and the MPE/iX logon. Data sources must be defined in the ODBC.INI file. For information on how to configure data sources in the ODBC.INI file, see the "readme.hp" file installed from the HP ODBC Driver Setup Disk. For systems that use ALLBASE/SQL or IMAGE/SQL on MPE/iX 5.0, refer to the HP PC API User's Guide. In this example, "PARTS" data source has been configured and describes the IMAGE/SQL database that contains inventory information. o Begin by verifying that the Data Access option in Microsoft Excel 5.0 is installed. Start Excel and look under the Data menu. If the last selection is "Get External Data", then the Data Access option is installed. If it is not installed, it can be added by running the Excel Setup program and selecting the Data Access option. o Once the Data Access option is installed, select "Get External Data" from the Data menu. As Microsoft Query begins, a dialogue box labeled "Select Data Source" is displayed. Select "Other". A dialogue box labeled "ODBC Data Sources" will be displayed with a list of the data sources defined in the ODBC.INI file. Click on the data source labeled "PARTS" and select "OK". The PARTS data source name will be added to the Microsoft Query list of data sources in the dialogue box labeled "Select Data Source". To connect to the PARTS data source, click on the PARTS data source and select "Use". o When connected to the database, Query will display a dialogue box labeled "Add Tables," with a list of available database tables. Click on the table, "Inventory," and select "Add". Query will display a list of column names for the table. Select "Close". o To retrieve all of the records in the underlying table, double-click the asterisk in the column list. Records to be displayed can be limited by specifying criteria. For more information, please see the Microsoft Query User's Guide. o Once data has been selected, click on "Return Data to Microsoft Excel" from the File menu. The data can now be manipulated in an Excel spreadsheet. The data, however, is local to the spreadsheet and any change here will not affect the data in the database. Kriss Rant is a software engineer specializing in client/server in HP's Commercial Systems Division's Research and Development Laboratory. --------------------------------------