XL5: Error/Incorrect Result Running SQL Function from Button

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you run a Visual Basic procedure that uses the SQLREQUEST function, the procedure returns an error message or an incorrect result if you run the procedure from a command button or from a toolbar button.

CAUSE

This behavior occurs only when you assign the procedure to a command button or a toolbar button, and run the procedure from the button. The correct result is returned when you run the procedure that contains the SQL function from the module sheet, or from the Macro dialog box.

WORKAROUND

To avoid receiving an incorrect result or an error message when you choose a toolbar button to run a procedure that uses the SQLRequest function, call the procedure from another procedure, and then do the following to assign the calling procedure to the toolbar:

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. 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."

1. In a new module, enter the following:

         Sub Macro1()
            ' Sample_SQL is the name of the procedure that
            ' contains the SQL function
   
            Application.OnTime Now, "Sample_SQL"
   
            ' Note, do not any other lines below the above line of code.
            ' If you want to add more code, add the lines below the SQLRequest
            ' function call in the "Sample_SQL" routine.
   
         End Sub
   
         ' Procedure that contains the SQL function
         Sub Sample_SQL()
            array1 = SQLRequest("DSN=Database1", "Select * From Table1")
         End Sub
   
   

2. From the View menu, choose Toolbars. Choose the Customize button. From the Categories list, select Custom. From the Buttons list, drag any button off of the dialog to create a custom button.

3. In the Assign Macro dialog box, select Macro1 (not Sample_SQL) and choose OK.

4. Close the Customize dialog box.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.

MORE INFORMATION

The SQLREQUEST function is available in the XLODBC.XLA. You can use this function in a Visual Basic procedure by creating a reference to the XLODBC.XLA file using the References dialog box (Tools menu).

REFERENCES

For more information about the SQLRequest Function, choose the Search button in the Visual Basic Reference and type:

   SQLRequest Function