Microsoft KB Archive/195585

= PRB: "SQL: Cannot Locate Table" Message w/ Modify View Command =

Article ID: 195585

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 3.0b for Macintosh
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q195585



SYMPTOMS
After issuing a Modify View command, the following message displays:

SQL: Cannot locate table.

Depending on the version of Visual FoxPro, after you click OK, one of the following occurs:

Visual FoxPro 3.0 or 5.0:

The View Designer opens without any fields displaying in the Selected Fields list and displays the available fields in the Available Fields list of the Filter tab with the format DATABASE_TABLE.FIELD rather than table.field.

Visual FoxPro 6.0:

The View Designer does not open.



CAUSE
This behavior is caused by incorrectly referencing a database in the CREATE SQL VIEW command used to create the view.

The error occurs when using the following syntax to create a remote SQL VIEW, using a Visual FoxPro database and table as the remote data source: CREATE SQL VIEW MY_SQL_VIEW REMOTE CONNECT MYREMOTE ; AS SELECT FIELD_1, FIELD_2, FIELD_3 ; FROM database!table ; ORDER BY table.field The error also occurs when using the following syntax to create a remote SQL VIEW, using a SQL Server table as the remote data source: CREATE SQL VIEW MY_SQL REMOTE CONNECT MYREMOTE ; AS SELECT FIELD_1, FIELD_2, FIELD_3 ; FROM database.owner.table ; ORDER BY table.field



RESOLUTION
Here are two resolutions:

 Use the View Designer.

-or-  If programmatically creating the remote SQL VIEW, check the syntax of the command used to create the remote SQL VIEW.

The correct syntax for creating a remote SQL VIEW, using a Visual FoxPro database and table as the remote data source follows: CREATE SQL VIEW MY_SQL_VIEW REMOTE CONNECT MYREMOTE ; AS SELECT FIELD_1, FIELD_2, FIELD_3 ; FROM table ; ORDER BY table.field The correct syntax for creating a remote SQL VIEW, using a SQL Server table as the remote data source follows: CREATE SQL VIEW MY_SQL REMOTE CONNECT MYREMOTE ; AS SELECT FIELD_1, FIELD_2, FIELD_3 ; FROM database.owner.table table ; ORDER BY table.field 



Steps to Reproduce Behavior
 Create an ODBC data source called MYREMOTE, to use in a remote SQL VIEW. MacIntosh users should create an ODBC data source using the PUBS database that ships with SQL Server. Windows users should create an ODBC data source using the TESTDATA files that ship with Visual FoxPro.  Copy the following code to a new program file and save it: CLOSE ALL SET SAFETY OFF SET MULTILOCKS ON     * Create a database. CREATE DATABASE MYREMOTE * Create a connection. CREATE CONNECTION MYREMOTE DATASOURCE "MyRemote" * Create a remote view. DO CASE CASE _MAC CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ; AS SELECT au_lname, au_fname ; FROM pubs.dbo.authors ; ORDER BY authors.au_lname CASE _WINDOWS CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ; AS SELECT cust_id, company, city ; FROM testdata!customer ; ORDER BY customer.cust_id ENDCASE USE test && Use the view BROWSE TIMEOUT 2 MODIFY VIEW test CLOSE ALL RETURN  Run the program file. Note that when you encounter the MODIFY VIEW command that the "SQL: Cannot locate table" message appears.</li>  Replace the DO CASE ... ENDCASE section of the program with the following code: DO CASE CASE _MAC CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ; AS SELECT au_lname, au_fname ; FROM pubs.dbo.authors authors; ORDER BY authors.au_lname CASE _WINDOWS CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ; AS SELECT cust_id, company, city ; FROM customer ; ORDER BY customer.cust_id ENDCASE </li> Run the program file. Note that when you encounter the MODIFY VIEW command, that the View Designer opens without any error message.</li></ol>

Keywords: kbsqlprog kbdatabase kbprb kbsample kbclientserver KB195585

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.