XL5: SQLRetrieve Fails When Returning Large Result Set

• Microsoft Excel for Windows NT, version 5.0

SYMPTOMS

The SQLRetrieve macro function may fail to return records to the specified worksheet. You may also receive the following error message if you use the SQLError macro function to trap errors produced by the SQLRetrieve function:

   Run-time error '9':
   Subscript out of range

CAUSE

If the result set being returned by SQLRetrieve contains a large number of elements (usually more than 5,461 cells), the SQLRetrieve function will return an 'Error 2042' error code and will fail to return the results to the specified worksheet location. The number of elements that can be returned is dependent on the available memory on your computer.

To determine how many elements are being returned by the query, multiply the number of columns in the result set by the number of rows. For example, a result set of 2 columns and 2,700 records contains 5,400 elements, or cells.

WORKAROUND

The following Visual Basic for Applications macro shows how to determine if SQLRetrieve failed under the circumstances stated above.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Sub GetData()
    ' Declare variables.
    Dim Chan As Variant, NumCols As Variant, NumRows As Variant

    ' Open a channel to the Nwind data source.
    Chan = SQLOpen("DSN=Nwind")

    ' Select all columns from the TEST.DBF file.
    NumCols = SQLExecquery(Chan, "SELECT * FROM test.dbf")

    ' Retrieve the resulting data to cell A1 on Sheet1 in the active
    ' workbook. If the file contains more than 5,461 elements, SQLRetrieve
    ' may return an 'Error 2042' to NumRows.
    NumRows = SQLRetrieve(Chan, Range("Sheet1!A1"), , , True)

    ' If NumRows evaluates to an error value, check the error type.
    If IsError(NumRows) Then
        ' If an error occurs, continue with the next line of code.
        On Error Resume Next
        MsgBox sqlerror()(3)
        ' If SQLRetrieve fails, SQLError may fail with Error '9',
        ' which means "Subscript out of range".
        If Err = 9 Then
            ' Reset the Err value.
            Err = 0
            MsgBox "No records retrieved; result set too large."
        End If
    End If

    ' Close the channel to Nwind.
    SQLClose Chan
End Sub

Workaround 2

Alternatively, you can use the SQLRetrievetoFile function to return the large data set to a text file and then open the text file in Microsoft Excel.

Sub GetData ()

   ' Declare variable.
   Dim Chan As Variant

   ' Open a channel to the Nwind data source.
   Chan = SQLOpen("DSN=Nwind")

   ' Select all columns from the TEST.DBF file.
   SQLExecquery Chan, "SELECT * FROM test.dbf"
   ' Retrieve the resulting data to a text file called "TEST.TXT"
   NumRows = SQLRetrievetofile(Chan, "TEST.TXT", True)

   ' Close the channel to Nwind.
   SQLClose Chan

   'Open the textfile TEST.TXT which contains the data
   Workbooks.Open "TEST.TXT"

End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The SQLRetrieve macro 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 from the Tools menu. For more information, the syntax for the SQLRetrieve function is shown below:

SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, _

     ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical)

REFERENCES

For more information on the References command:

• Search for "References command (Tools menu)" using the Help menu
• See "Calling Procedures in Another Workbook," in the Visual Basic User's Guide

   SQLRetrieve