Four ways to access remote data in Excel 2000

excel| Access | Data Excel 2000 as a spreadsheet software, it is not only powerful data processing capabilities, but also its report function is very powerful. It is often used in Excel 2000 to invoke the content of large databases built by database software such as access, SQL Server, Oracle, DB2, and so on. It is convenient for users to filter, sort, query, edit, and print reports on the worksheet, which is familiar to most people. But how do you call this data? I am here to provide 4 kinds of methods.
The following four methods must first create a data source, and we take the sample database pubs in SQL Server7.0 as an example. /ODBC the data source/System DSN in the Control Panel, click the Add button, select the SQL Server driver, and create a named pubs name data source that is connected to the pubs database.
Method One:
In Excel 2000, select data/Get External data/create a new database query, and then follow the wizard prompts Step-by-step, and finally return the data to Excel 2000 in the line
This method is commonly used and is the most convenient. However, this method can only query remote data and cannot add and modify remote data.
Method Two:
This method requires VBA programming (methods three, same as method four), in Visual Basic programming, we often use ADO to access the data. ADO is a new high-level programming interface provided by Microsoft for a wide variety of data sources. It supports most database operations and is an ideal way to use ADO in Excel 2000 to access data. Take a look at one of the following examples:
Sub opendb ()
Dim cn as ADODB. Connection
Dim rs as ADODB. Recordset
Set cn = New ADODB. Connection
Set rs = 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
The first to second sentence of the macro defines an ADO Connection object and a Recordset object, and the third to fourth sentence creates an ADO Connection object and a Recordset object. The last sentence connects to the data source pubs database we built earlier. The sixth sentence is that an ADO Recordset object executes a single SQL SELECT statement. In this you can also execute insert,update and so on SQL statements. The seventh sentence is to return the records in RS to the current table. The eighth to nineth sentence is to close the connection. A library file that references ADO in Excel 2000 tools/references before running.
Applying ADO objects in Excel 2000 can not only query the database, but also increase, modify the records of the database, even call the SQL SERVER7.0 stored procedures, and enhance the ability of Excel 2000 for database processing.
Method Three:
The functionality of Excel 2000 is very powerful, it brings an ODBC add-in, we refer to this macro file Xlodbc.xla in Excel 2000, we can access the data source we have created in the following ways
Sub opendb ()
Dim ID As Integer
id = SQLOpen ("Dsn=pubs")
SQLExecQuery ID, "SELECT * from Authors"
Set output = Worksheets ("Sheet1"). Range ("A1")
SQLRetrieve ID, output,,,
True SQLClose ID
End Sub
The second sentence is to call the SQLOpen function in the XLODBC.XLA macro to establish a connection to the database pubs, and the third sentence is that the SQLExecQuery function executes a SELECT statement, which is the unique connection ID returned by the SQLOpen function. The SQLRetrieve function returns the results of the query executed by the SQLExecQuery function to Excel 2000.
This programming method can be easily said and used easily. It is also a good way to access remote data.
Method Four:
This approach is to use the API method, and here is an example
' Assigning an environment handle
res = Sqlallochandle (sql_handle_env, sql_null_henv, henv)
' Set environment properties
res = SQLSetEnvAttr (henv, sql_attr_odbc_version, SQL_OV_ODBC3, 0)
' Assign database connection handle
res = Sqlallochandle (SQL_HANDLE_DBC, henv, Hsvr)
' Connect to a data source
sconnect= "Dsn=pubs;database=pubs"
Res=sqldriverconnect (Hsvr,byval,0&,sconnect,len (Sconnect), Sconnout, Len (Sconnout), _ Nconnoutlen, 0)
sSQL = "SELECT * FROM Authors"
res = SQLExecDirect (Hsel, sSQL, Len (sSQL))
' Returns the record to the current table
Res=sqlnumresultcols (Hsel, Nc)
Do While (SQLFetch (Hsel) <> sql_no_data_found)
J=j+1
For I=1 to Nc
res = SQLGetData (Hsel, I, Sql_c_char, TMP, L, PL)
Cells (J, i) =tmp
Next I
Loop
' Release the database connection handle
res = Sqlallochandle (sql_handle_stmt, Hsvr, Hsel)
' Release the database connection handle
ret = Sqlfreehandle (sql_handle_env, henv)
This method is more complex, debugging is also more difficult, but fast, in a large number of query data and their own in the API calls more familiar, you can use this method. When you use the above instance, you have to do the definition of API declarations and constants.