SQLRetrieveToFile Function

Excel Developer Reference

It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRetrieveToFile and the other ODBC functions in the Xlodbc.xla add-in.

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 (in the Tools menu) in the Visual Basic Editor.

SQLRetrieveToFile(ConnectionNum, Destination, ColNamesLogical, ColumnDelimiter)

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.

Example

This example runs a query on the Northwind database. The result of the query, which is a list of all products that are currently on order, is written as the delimited text file Output.txt in the current folder.

  databaseName = "Northwind"
queryString = _
    "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
SQLRetrieveToFile chan, "OUTPUT.TXT", True
SQLClose chan