SQLRetrieveToFile Function

See Also         Example

Don’t use SQLRetrieveToFile and the other ODBC functions in the Xlodbc.xla add-in; use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead. For more information about ActiveX Data Objects, see Welcome To ADO.

SQLRetrieveToFile retrieves all 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 by using SQLOpen, executed a query by using SQLExecQuery, and have the results of the query pending.

This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by 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 that were generated by SQLExecQuery.

If ConnectionNum isn’t 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 doesn’t 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 to have the column names be returned as the first row of data. False or omitted to have the column names not be 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 use ";" 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 doesn’t 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 by using SQLOpen.

  2. Use the connection ID returned by SQLOpen to send a query with SQLExecQuery.