Open Database Connectivity Add-in

Removed in 2003
This was replaced with ActiveX Data Objects.
This is short for Open Database Connectivity and allows you to connect to external data sources using ODBC drivers and the SQL.REQUEST function.
This add-in was not installed as part of the normal installation but could be downloaded from the Microsoft website.
The add-in has the filename "XLODBC.xla" and was installed in the following folder by default:
C:\Program Files\Microsoft Office\Office10\Library\Msquery\


SQL.REQUEST worksheet function

SQL.REQUEST(connection_string, output_ref, driver_prompt, query_text, col_names_logical)
Connects to an external data source and executes a query.


connection_string - The data source name, user id and password required by the driver.
output_ref - This is only relevant when you are not calling this function from a worksheet.
driver_prompt - The option for whether the driver prompt dialog box is displayed:. 1 = dialog is always displayed. 2 = dialog is only displayed when there is insufficient information (options are available). 3 = dialog is only displayed when there is insufficient information (options are not available). 4 = dialog is not displayed and an error is returned if unsuccessful
query_text - The SQL statement you want to execute.
col_names_logical - True or False to specify if column names are returned as part of the result.


This function did not appear in the 'Insert Function' dialog box.
Using this old Excel 7.0 add-in is not recommended as the ADO (ActiveX Data Object) library has since become the preferred method for connecting to external data sources.
The "connection_string" must be in the exact format for that particular driver.
The "connection_string" for dBase might be - DSN=Nwind;PWD=password.
The "connection_string" for SQL Server might be - DSN=ServerName;Database=People;UID=name;PWD=password.
The "connection_string" for Oracle might be - DNS=OracleDataSource;DBQ=Name;UID=name;PWD=password.
The "connection_string" must always contain the DNS (Data Source Name).
The "connection_string" can be entered as an array.
If the "connection_string" is longer than 250 characters you must enter it as an array.
If the "connection_string" is not valid and a connection cannot be made, then #N/A! is returned.
If "driver_prompt" is left blank, then 2 is used.
The "query_text" can be made up of cell references.
If "query_text" is longer than 255 characters then split the query into a group of vertical cells and pass in the cell range.
If "query_text" is invalid SQL or cannot be executed, then #N/A! is returned.
If "col_names_logical" is left blank, then False is used.
If this function is successful it will either return the result as an array or the number of rows affected.


SQL.REQUEST("DSN=Nwind;DBQ=C:\Temp\MsQuery;FIL=dBASE4", A1, 2, "SELECT Customer_ID FROM Orders WHERE OrderNo > 200", True)
SQL.REQUEST("DSN=MS Access Database;DBQ=C:\Personal\Databases\NorthWind\Nwind.mdb",,,"SELECT City FROM Customers WHERE (CustomerID='AROUT')")


SQLBind

Specifies storage for a result column.


SQLClose

Closes a data source connection.


SQLError

Returns detailed error information.


SQLExecQuery

Sends a query to a data source.


SQLGetSchema

Gets information about a connected data source.


SQLOpen

Establishes a connection to a data source.


SQLRetrieve

Retrieves query results.


SQLRetrieveToFile

Retrieves query results and places them in a file.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext