DOCUMENT:Q133470  15-FEB-2000  [foxpro]
TITLE   :PRB: Cannot Modify Table Structure After Using SQL SELECT
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 3.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

After performing an SQL SELECT statement on a table, the table structure becomes
read-only, so you can't modify it.

CAUSE
=====

Initially the SELECT statement is processed to see if the result set can be
obtained from the original table with the application of a filter. If it can, a
temporary cursor is not created on disk; instead, a filtered view of the
original table is created. You will see a cursor object in the View window with
the name QUERY, this is an alias of the source table.

Because you are looking at a filtered view of the original table, the MODIFY
STRUCTURE command won't work; the underlying table structure of the result set
has been temporarily frozen.

RESOLUTION
==========

To execute an immediate MODIFY STRUCTURE command on the source table, you must
first close the QUERY cursor. Alternatively, you could avoid this by editing the
query to involve complexities that would force a temporary, physically distinct
cursor to be created.

STATUS
======

This behavior is by design.

MORE INFORMATION
================

Code to Reproduce Behavior
--------------------------

The following test code demonstrates this principle with a very simple
single-table query:

        CREATE TABLE TEST (Name C(20))         && Create temporary table
      CLOSE ALL                              && Close all tables
      USE TEST EXCLUSIVE                     && Re-use the table
      INSERT INTO TEST VALUES ("Bob")        && Populate with some data
      SELECT * FROM TEST INTO CURSOR QUERY   && Execute query
      SELECT TEST                            && Re-select the table
      MODIFY STRUCTURE                       && Command fails.

The following example shows a more complex query. Consequently, Visual FoxPro
creates a cursor object named QUERY on the hard disk instead of creating a
filtered alias.

      USE SYS(2004)+"\SAMPLES\DATA\CUSTOMER" IN 0
      SELECT * FROM CUSTOMER WHERE REGION="OR" INTO CURSOR QUERY
      SELECT CUSTOMER
      MODIFY STRUCTURE

Additional query words: VFoxWin

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300
Version           : WINDOWS:3.0

=============================================================================

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.

Copyright Microsoft Corporation 2000.