DOCUMENT:Q191344 23-FEB-2000 [foxpro] TITLE :PRB: Base Table Fields Changed with Remote View PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:2.5,5.0,5.0a,6.0 OPER/SYS: KEYWORDS:kbDatabase kbODBC kbvfp kbvfp500 kbvfp500a kbvfp600 kbMDAC250 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a, 6.0 - Microsoft Data Access Components version 2.5 ------------------------------------------------------------------------------- SYMPTOMS ======== When issuing a REQUERY command against a remote view, while a second remote view is being USEd, the following error message appears: Base table fields have been changed and no longer match view fields. View field properties cannot be set. CAUSE ===== This behavior occurs when two or more views are defined with the following attributes: - The views were created using the same named connection to a remote data source. - The views were created using the SHARED clause in the CREATE SQL VIEW command. - The Prepared property of the views is set to .T. RESOLUTION ========== 1. Do not include the SHARED clause in the CREATE SQL VIEW command used to create the remote view: CREATE SQL VIEW "MYVIEW1" ; REMOTE CONNECT "CONN1" ; AS SELECT * ; FROM mytable_a DBSETPROP('MYVIEW1', 'View', 'Prepared', .T.) CREATE SQL VIEW "MYVIEW2" ; REMOTE CONNECT "CONN1" ; AS SELECT * ; FROM mytable_b DBSETPROP('MYVIEW2', 'View', 'Prepared', .T.) 2. Create a separate connection for each of the views: CREATE CONNECTION CONN1 DATASOURCE 'CONNECT' USERID 'SA' PASSWORD '' CREATE CONNECTION CONN2 DATASOURCE 'CONNECT' USERID 'SA' PASSWORD '' CREATE SQL VIEW "MYVIEW1" ; REMOTE CONNECT "CONN1" SHARED ; AS SELECT * ; FROM mytable_a DBSETPROP('MYVIEW1', 'View', 'Prepared', .T.) CREATE SQL VIEW "MYVIEW2" ; REMOTE CONNECT "CONN2" SHARED ; AS SELECT * ; FROM mytable_b DBSETPROP('MYVIEW2', 'View', 'Prepared', .T.) 3. Use the default PREPARED property: CREATE SQL VIEW "MYVIEW1" ; REMOTE CONNECT "CONN1" ; AS SELECT * ; FROM mytable_a USE MYVIEW1 IN 0 CREATE SQL VIEW "MYVIEW2" ; REMOTE CONNECT "CONN1" ; AS SELECT * ; FROM mytable_b STATUS ====== Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available. MORE INFORMATION ================ The default values for View properties are listed in the following table: Property Name Default Value -------------------------------------- SendUpdates .F. (False) UpdateType 1 SQL Update WhereType 3 Key and Modified Fields BatchUpdateCount 1 UseMemoSize 255 FetchSize 100 FetchMemo .T. (True) MaxRecords -1 (All) Tables Database Container and Table Name Comment Null String Prepared .F. (False) CompareMemo .T. (True) FetchAsNeeded .F. (False) Steps to Reproduce Behavior --------------------------- 1. Use the following code snippet to create a database container and two tables that will serve as a data source: CREATE DATABASE TESTODBC CREATE TABLE mytable_a ( ; FLD_A I NULL, FLD_C C(15) NULL) CREATE TABLE mytable_b ( ; FLD_B I NULL,FLD_A I NULL,FLD_C C(15) NULL) INSERT INTO mytable_a (FLD_A,FLD_C) VALUES (10452,'TEST1') INSERT INTO mytable_b (FLD_B,FLD_A,FLD_C) VALUES (1,22345,'TEST1') CLOSE ALL 2. Open the 32-bit ODBC Data Source Administrator and create an ODBC data source called "CONNECT" with a path to the Testodbc.dbc file, created in step 1. 3. Create a .prg file named Odbcdemo.prg, using the following code: * Begin Code CLOSE ALL SET SAFETY OFF CREATE DATABASE odbcdemo CREATE CONNECTION CONN1 DATASOURCE 'CONNECT' USERID 'SA' PASSWORD '' CREATE SQL VIEW "MYVIEW1" ; REMOTE CONNECT "CONN1" SHARED ; AS SELECT * ; FROM MYTABLE_A DBSETPROP('MYVIEW1', 'View', 'Prepared', .T.) CREATE SQL VIEW "MYVIEW2" ; REMOTE CONNECT "CONN1" SHARED ; AS SELECT * ; FROM MYTABLE_B DBSETPROP('MYVIEW2', 'View', 'Prepared', .T.) CLOSE ALL OPEN DATA odbcdemo USE MYVIEW1 USE MYVIEW2 IN 0 =REQUERY('MYVIEW1') && FAILS HERE. =REQUERY('MYVIEW2') =REQUERY('MYVIEW1') =REQUERY('MYVIEW2') 4. From the command line type the following: DO ODBCDEMO NOTE: Observe that the code fails during the attempt to issue a REQUERY command on MYVIEW1. 5. Edit the Odbcdemo.prg file and modify the code to reflect each of the preceding workarounds. Run the program after each change. REFERENCES ========== For additional information, please see the following article in the Microsoft Knowledge Base: Q178760 HOWTO: Alter the Properties of a View at Run Time (c) Microsoft Corporation 1997, All Rights Reserved. Contributions by John R. Desch, Microsoft Corporation Additional query words: ====================================================================== Keywords : kbDatabase kbODBC kbvfp kbvfp500 kbvfp500a kbvfp600 kbMDAC250 Technology : kbVFPsearch kbAudDeveloper kbMDACSearch kbMDAC250 kbVFP500 kbVFP600 kbVFP500a Version : WINDOWS:2.5,5.0,5.0a,6.0 Issue type : kbprb Solution Type : kbpending ============================================================================= 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.