Microsoft KB Archive/121598

= ErrorText Item Not Returned Using DDE =

Article ID: 121598

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Query 2000

-



This article was previously published under Q121598



SYMPTOMS
When you use dynamic data exchange (DDE) to access data in Microsoft Query, the ErrorText item does not return any errors that have occurred if you have requested any of the following items:

DataSourceName

Logon

UserName



CAUSE
This behavior occurs if you request one of the items above in a DDE conversation with Microsoft Query, and you then request the ErrorText item.

For example, if an error occurs when you execute an SQL statement using DDE, and you request the Logon item after this error occurs, the text of the error from the SQL statement is not returned if the command that requests the ErrorText item follows the command that requests the Logon item.

This is by design of Microsoft Query. This behavior occurs because the requests listed above contain ODBC calls, and the ErrorText item is reset depending on the outcome of these ODBC calls.



WORKAROUND
To work around this behavior, request the ErrorText item immediately after you run an SQL statement that you want to check for errors. Request the DataSourceName, Logon, or UserName items for a query channel or active query on a system channel either before you run an SQL statement, or after you request the ErrorText item.

The following is an example using a Visual Basic module in Microsoft Excel version 5.0 that demonstrates returning the ErrorText item using DDE statements: Sub Run_Query

' Dimension variables used Dim chan As Integer, sql As String Dim dde_err As Variant, log_on As Variant

' Define sql statement sql = "[open('SELECT customer.CUSTMR_ID FROM c:data\customer.dbf')]" ' Establish DDE channel with Microsoft Query chan = DDEInitiate("MSQUERY", "System")

' Logon using dBase4 data source DDEExecute chan, "[Logon('dBase4')]"

' Request Logon item log_on = DDERequest(chan, "Logon")

' Run SQL statement DDEExecute chan, sql

' Request ErrorText item dde_err = DDERequest(chan, "ErrorText") ' Display ErrorText item in a message box MsgBox dde_err(1)

' Terminate DDE channel DDETerminate chan

End Sub 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.



MORE INFORMATION
The DataSourceName item is available when you open a DDE channel with Microsoft Query using the System topic. The Logon and UserName items are available when you open a DDE channel with Microsoft Query using the window name in Microsoft Query as the topic.

