Microsoft KB Archive/119259

{|
 * width="100%"|

XL5: Error/Incorrect Result Running SQL Function from Button

 * }

-

The information in this article applies to:


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

  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  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. In the Assign Macro dialog box, select Macro1 (not Sample_SQL) and choose OK. 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).