Description
In Microsoft Excel for Windows 95, do not use SQLRetrieveToFile and the other ODBC functions in the XLODBC.XLA add-in; use the objects, methods, and properties in the Data Access Objects (DAO) library instead.
SQLRetrieveToFile retrieves all of the results from a previously executed query and places them in a file.
To use this function you must have established a connection with a data source using SQLOpen, executed a query using SQLExecQuery, and have the results of the query pending.
This function is contained in the XLODBC.XLA add-in (ODBC Add-In on the Macintosh). Before you use the function, you must establish a reference to the add-in using the References command (Tools menu).
Syntax
SQLRetrieveToFile(connectionNum, destination, colNamesLogical, columnDelimiter)
connectionNum
Required. The unique connection ID returned by SQLOpen and for which you have pending query results generated by SQLExecQuery.
If connectionNum is not valid, SQLExecQuery returns Error 2015.
destination
Required. A string that specifies the name and path of the file where you want to place the results. If the file exists, its contents are replaced with the query results. If the file does not exist, SQLRetrieveToFile creates and opens the file and fills it with the results.
The format of the data in the file is compatible with the Microsoft Excel .CSV (comma-separated value) file format.
Columns are separated by the character specified by columnDelimiter, and the individual rows are separated by a carriage return.
If the file specified by destination cannot be opened, SQLRetrieveToFile returns Error 2042.
colNamesLogical
Optional. True if you want the column names to be returned as the first row of data. False or omitted if you do not want the column names returned.
columnDelimiter
Optional. A string that specifies the character used to separate the elements in each row. For example, use "," to specify a comma delimiter or ";" to specify a semicolon delimiter. If you omit columnDelimiter, the list separator character is used.
Return Value
If successful, SQLRetrieveToFile returns the query results, writes them to a file, and then returns the number of rows that were written to the file.
If SQLRetrieveToFile is unable to retrieve the results, it returns Error 2042, and does not write the file.
If there are no pending results on the connection, SQLRetrieveToFile returns Error 2042.
Remarks
Before calling SQLRetrieveToFile, you must do the following:
1. Establish a connection with a data source using SQLOpen.
2. Use the connection ID returned by SQLOpen to send a query with SQLExecQuery.
See Also
SQLBind Function, SQLClose Function, SQLError Function, SQLExecQuery Function, SQLGetSchema Function, SQLOpen Function, SQLRequest Function, SQLRetrieve Function.
Example
This example runs a query on the NWind sample database. The result of the query, which is a list of all products that are currently on order, is written as a delimited text file, OUTPUT.TXT, in the current directory or folder.
If Application.OperatingSystem Like "*Win*" Then
databaseName = "NWind"
Else 'Macintosh
databaseName = "NorthWind"
End If
queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
SQLRetrieveToFile chan, "OUTPUT.TXT", True
SQLClose chan