ID: Q126896
The information in this article applies to:
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.
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".
Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/supportnet/refguide/
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
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: 7.00 5.00 VBA_XL.HLP
Keywords :
Version : WINDOWS:5.0,5.0c,7.0,7.0a; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWSLast Reviewed: May 17, 1999