Local View with multiple remote tables

[EnS]

Hello

Can one of you gents advise me on how I can create a local view in a dbc using multiple remote tables? I have a main table "tblMain.dbf" in "loc1" and "tblOther1.dbf", "tblOther2.dbf", and "tblOther3.dbf" in "loc2". The table definitions are as follows:
tblMain.dbf
- rec_id
- other_id_1
- other_id_2
- other_id_3

tblOther1.dbf
- other_id
- other_desc

tblOther2.dbf
- other_id
- other_desc

tblOther3.dbf
- other_id
- other_desc

I would like to get all tblMain records and only those of the “other” tables that match setting the join conditions, either “Left Outer” or “Right Outer” as follows:
- tblMain.other_id_1 = tblOther1.other_id
- tblMain.other_id_2 = tblOther2.other_id
- tblMain.other_id_3 = tblOther3.other_id

Creating a local view with the following results:
lcv_View
- rec_id
- other_id_1
- other_desc_1
- other_id_2
- other_desc_2
- other_id_3
- other_desc_3

The wizard returns an error saying that the same table, “tblMain”, cannot be the parent to more than 1 child table. I then played around with the “Join” condition only to have the wizard tell me that the same table, “tblMain”, cannot be the child to more than 1 parent table. I’ve also tried using the “CREATE SQL VIEW” statement only to have a dialog box popup asking for the table’s location. Furthermore, I tried creating a remote view, which worked fine, then created the local view from the remote view. However, when I try to connect to the local view in Access via ODBC, I receive an error telling me that the function is not supported on remote tables. Is there a way I can do this?

Thanks in advance.

P.S. - I'm using VFP 6.0


ERM

 

[rgbean]

ERM,
The View Designer's before VFP 8.0 are a bit limited.

You can either write the code manually, or try using the free ViewEditor at

http://www.geeksandgurus.com/vfptools/vieweditor.htm.

Rick