Knowledge Base

PRB: Base Table Fields Changed with Remote View

Article ID: 191344

Article Last Modified on 3/3/2005

APPLIES TO

Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 5.0a
Microsoft Visual FoxPro 6.0 Professional Edition
Microsoft Data Access Components 2.5

This article was previously published under Q191344

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

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.)

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.)

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

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

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.

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')

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.
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:

178760 HOWTO: Alter the Properties of a View at Run Time

Keywords: kbdatabase kbprb kbpending kbcode KB191344

Microsoft KB Archive/191344

Contents