Microsoft KB Archive/126896

= SQLRetrieve Returns Error When No Records Found =

Article ID: 126896

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q126896



SYMPTOMS
In the versions of Microsoft Excel listed above, when you use the return value of the SQLRetrieve function and the query does not return any data, the return value is an error rather than the expected value 0.

The help topic for SQLRetrieve in Visual Basic Help incorrectly states:

SQLRetrieve returns the number of rows in the result set. If SQLRetrieve is unable to retrieve the results on the specified data source or if there are no results pending, it returns Error 2042. If no data is found, it returns 0.



WORKAROUND
Although the SQLRetrieve command returns an error because no records match the query, the SQLExecquery command successfully returns the number of columns.

Therefore, to determine that no records were found, you can create a macro that tests whether the value returned by SQLExecquery is numeric and the value returned by SQLRetrieve is an error. If these cases are true, no records were found.

For example, the following sample macro produces a message box stating "No Records Retrieved", because there are no records in the EMPLOYEE.DBF table with the Last_Name "Doe".

Sample Macro
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Sub GetData Dim r, c

'Establish a channel chan = sqlopen("dsn=nwind")

'Execute the query and return the number of columns to c      c = sqlexecquery(chan, "select * from employee where   last_name='Doe'")

'Retrieve the data and return the number of rows to r      r = sqlretrieve(chan, ActiveCell)

'If r is an error and c is a number, assume that no records were 'retrieved If IsError(r) And IsNumeric(c) Then MsgBox "no records retrieved"

'close the channel sqlclose chan

End Sub



STATUS
Microsoft has confirmed this to be a problem in the products listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

Additional query words: VBA_XL.HLP XL

Keywords: KB126896

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.