Knowledge Base

You receive an ODBC driver error message when you set a Left Outer Join between two tables in Visual FoxPro

Article ID: 191695

Article Last Modified on 3/10/2005

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
Microsoft Data Access Components 2.6
Microsoft Visual FoxPro 7.0 Professional Edition

This article was previously published under Q191695

SYMPTOMS

When creating a remote view to a FoxPro table using the Visual FoxPro ODBC driver, the following error occurs when you set a Left Outer Join in the Join tab between two tables:

Connectivity error: [Microsoft][ODBC Visual FoxPro Driver]Command Contains unrecognized phrase/keyword.

RESOLUTION

Access the FoxPro tables directly, using the SELECT-SQL command. For example:

   CREATE SQL VIEW test CONNECTION myconn;
   AS SELECT * FROM orders LEFT OUTER JOIN orditems;
   ON orders.order_id = orditems.order_id

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem is fixed in Visual FoxPro 8.0 and 9.0.

MORE INFORMATION

Steps to Reproduce the Behavior

Create a datasource to the Visual FoxPro ODBC driver in the ODBC Administrator pointing to the Testdata.dbc sample database.
Open any .dbc in Visual FoxPro and create a remote view using the datasource created in step 1.
Add the Orders.dbf and Orditems.dbf using the join condition of the field order_id.
Close the Open dialog box and select the Add All button in the Fields tab of the View Designer.
Select the Join tab and in the Type drop-down list box, select Left Outer Join. In the Field Name drop-down list box, select Orders.order_id. In the Value drop-down list box, select Orditems.order_id.
Run the view and note that the error occurs.

Additional query words: kbVFp500a kbVFp600 kbODBC

Keywords: kbnofix kbdatabase kbprb KB191695