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