XL: SQLRetrieve Returns Error When No Records Found

ID: Q126896

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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, 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/support/supportnet/overview/overview.asp


   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

Keywords :
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type :