SQL.QUER Returns #REF in Q+E

Last reviewed: November 2, 1994
Article ID: Q74241

SUMMARY

The function SQL.QUERY is equivalent to the command Select SQL Query. This option is not available unless a query window is open. If you use the EXECUTE function to send the SQL.QUERY command using a DDE channel, #REF will be returned unless there is an open Query in Q+E.

MORE INFORMATION

The following example will NOT work because there is not an open Query in Q+E:

   A1    chan=INITIATE("qe","system")
   A2    =EXECUTE(chan,"[sql.query('select * from dBASEfile |
          c:\excel\qe\emp.dbf WHERE ')]")
   A3    =TERMINATE(chan)
   A4    =RETURN()

The next example WILL work because it sends commands to Q+E that open a query window before attempting to modify the query:

   A1   chan=INITIATE("qe","system")
   A2   =EXECUTE(chan,"[open('d:\excel\xl3\qe\emp.dbf')]")
   A3   =EXECUTE(chan,"[sql.query('select * from
         d:\excel\xl3\qe\addr.dbf where state=''NC''')]")
   A4   =TERMINATE(chan)
   A5   =RETURN()

An alternative to opening a file before using SQL.QUERY is to use the OPEN command to send your SQL statement:

   A1   chan=INITIATE("qe","system")
   A2   =EXECUTE(chan,"[open('select * from
         d:\excel\xl3\qe\emp.dbf WHERE salary > 30000')]")
   A3   =TERMINATE(chan)
   A4   =RETURN()

The following are other reasons that the line in your macro containing the EXECUTE and SQL.QUERY commands might fail even though the other lines execute correctly:

You are attempting to access a file or table that is not supported by the currently selected driver. An example would be sending the statement "Select * from EMP.XLS" when dBASEFile is set up as your default driver. This can be prevented by including the correct driver name in the Select statement. For example:
```
      "Select * from ExcelFile | EMP.XLS".
```
You are attempting to access a file that is not in the current directory and you have not included the full path to the file.
You are extremely low on memory or system resources.

REFERENCES

"Q+E for Microsoft Excel User's Guide," pages 70-71, 112

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.