MSQuery: ErrorText Item Not Returned Using DDE

ID: Q121598

1.00 2.00 WINDOWS kbtool kbprg kbcode

The information in this article applies to:

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.

REFERENCES

"Microsoft Query User's Guide," Chapter 9, "Using Dynamic Data Exchange with Microsoft Query"

KBCategory: kbtool kbprg kbcode KBSubcategory: xlquery

Additional reference words: 1.00 2.00 5.00 5.00c 6.00 6.00a 6.00c 7.00 officeinterop empty null blank 

Keywords          : xlquery 
Version           : 1.00 2.00
Platform          : WINDOWS

Last Reviewed: September 16, 1996