XL5: Visual Basic Example Using the SQLError Function

ID: Q122577

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c

SUMMARY

This article explains how to use the SQLError function in a Visual Basic, Applications Edition, macro in Microsoft Excel version 5.0.

MORE INFORMATION

In Microsoft Excel version 5.0, the XLODBC.XLA add-in file includes 7 SQL functions for accessing external data sources with ODBC (Open Database Connectivity) drivers. One of these functions, SQLError, returns detailed error information when you call it after one of the other ODBC SQL functions fails. If SQLError itself fails, it cannot return any error information.

If an ODBC function such as SQLExecQuery fails, the error information is defined and stored in memory. You can then make a call to the SQLError function and assign this information to an array variable. The information returned by SQLError has three formats:

If there are no errors from a previous ODBC function call, this function returns only the #N/A error value to a one-dimensional array with one element. This error value may also be shown as "Error 2042" in the Debug window when you debug your macro.

If only one error is reported, SQLError will return a one-dimensional array with three elements.

If a function call generates multiple errors, SQLError returns a two- dimensional array in which each row describes a single error.

In the last two cases described above, each row has three fields. The fields are:

A character string indicating the ODBC error class and subclass.

A numeric value indicating the data source native error code.

A text message describing the error.

Below is a Visual Basic macro that illustrates how to use the SQLError function to capture SQL errors.

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. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

NOTE: This example requires that you reference the XLODBC.XLA file using the References dialog box in the module that contains this procedure.


Sub SQLError_Example()
  'This example will return an error because
  'the data source being opened doesn't exist.

  'Declare variables
   Dim Channel As Variant      'Channel number
   Dim MyErrorArray As Variant 'Variable to store SQLError array
   Dim LF As String            'Line Feed variable

   LF = Chr(10)                'Assign value for Line Feed

  'Open a channel to the Wind data source. This line will
  'produce an error because the data source 'Wind' doesn't exist.
  Channel = SQLOpen("DSN=Wind")

 'Check for possible error value in the Channel variable.
  If IsError(Channel) Then
  'Assign the elements of the SQLError array to MyErrorArray.
   MyErrorArray = SQLError()
   'Display the individual error elements of the MyErrorArray array.
   'Add Line Feed character for readability.
    MsgBox MyErrorArray(1) & LF & MyErrorArray(2) & LF & MyErrorArray(3)

   'Exit sub procedure
    Exit Sub
   End If

 'Close the channel to Wind.
  SQLClose Channel
End Sub

When you run this macro, you will receive an error message similar to the following:

IM002 0 [Microsoft][ODBC DLL] Data source not found and no default driver specified

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 9, "Using the Built-in Error Values of Microsoft Excel"

For more information about SQLError, choose the Search button in Programming With Visual Basic Help, and type:

SQLError Function

Additional query words: Isnumeric

Keywords :
Version : WINDOWS:5.0,5.0c
Platform : WINDOWS
Issue type :