Excel 2000 As an spreadsheet software, it is not only powerful data processing power, but also its reporting function is also very powerful. Therefore, the contents of large databases established by the database software such as Access, SQL Server, Oracle, DB2 are often used in Excel 2000. Users can filter, sort, query, edit, and print reports in a worksheet, which is very convenient, which is also familiar to most people. But how do I call this data? I have four methods here. The following four methods must first create a data source, we take the sample database PUBS in SQL Server 7.0 as an example. In the Control Panel / ODBC Data Source / System DSN, click Add New, select the SQL Server driver to create a Pubs name data source connected to the PUBS database. Method 1: In Excel 2000, select Data / Get External Data / New Database Query, and then press the prompt step by step, and finally return the data in Excel 2000, this method is common, and most convenient. But this method can only query remote data and cannot be added and modified for remote data. Method 2: This method requires VBA programming (the method three, method four), in Visual Basic programming, we often use ADO to access data. ADO is a new advanced programming interface provided by Microsoft to a wide variety of data sources. It supports most database operations, which use ADO to access data in Excel 2000, which is a very ideal way. Take a look at the following example: SUB OPENDB () DIM CN AS AdoDb.Connection Dim Rs as adoDb.recordset set cn = new adoDb.connection set = new adoDB.recordset cn.open "provider = msdasql.1; persist security info = False; data source = pubs "rs.open" Select * from authors ", CN RANGE (" A1 "). CopyFromRecordset RS rs.close cn.close End Sub Macro in the first, two sentences define an ADO's Connection object and A RecordSet object, third, four sentence created an ADO's Connection object and a Recordset object. The fifth sentence is connected to the previously established data source PUBS database. The sixth sentence is the ADO's RecordSet object enforces a SQL SELECT statement. This is also possible to perform Insert, Update, etc. SQL statement. The seventh sentence is to return the record in the RS to the current table. Eighth, nine sentences are closed. Before run, you will reference the ADO library file in the Excel 2000 tool / reference. Applying an ADO object in Excel 2000, not only the database can be queried, but also, modify the database record, and even call the SQL Server 7.0 stored procedure, enhance the Excel 2000 on the database processing power.
