>>>I have a table with FKs in it.
>>>I need an updateable view that has fields from the "parent" tables in it. Only 1 table needs to be updateable, the rest of the tables are just for display.
>>>
>>>I have no problem doing:
>>>
>>>select a.Fields, b.Field1, c.Field2 ;
>>> from a, b, c ;
>>> where a.FK1 = b.PK ;
>>> and a.FK2 = c.PK
>>>
>>>but when I try to create it in the View Designer, sometimes I'll get c.Field2 and sometimes not. Sometimes it'll work for a while, but then I'll try to add a third FK and then lose c.Field2.
>>>
>>>I really need to get 5 FKs to pull their respective fields into the View (2 from table b, 2 from table c, and 1 from table d).
>>>
>>>TIA
>>
>>Bill,
>>I'm not sure view designer could handle it. I'd design it w/o designer < g >
>>Try to use 'join' instead of old style joins.
>>
>>
>>* create sql view myView as ;
>>
>>select a.Fields, b.Field1, c.Field2 ;
>> from force ;
>> Table1 a ;
>> inner join Table2 b on a.FK1 = b.PK ;
>> inner join Table3 c on a.FK2 = c.PK
>>
>>'Force' clause not only forces the connections made in that order but prevents the view to be opened and possibly screwed by the designer.
>>You'd need manually adding update properties. Use gendbc to create a regular view to check how it writes. Adopt to your code and run to create your view.
>>Alternatively you could use Erik Moore's eview utility I think.
>>Cetin
>
>Hi Cetin,
>I'm going to look at Erik's eview, but in the meantime, maybe you can tell me what I'm doing wrong.
>I did a Create Sql View like you said.
>It doesn't show up on my list of views in the dbc in the project, but if I type "modify view " intellisense lists it as a view available. If I select it I get a "not a character expression" error (which is fine).
>I can't figure out how to USE it though. When I type use < DBC!VIEW >, intellisense doesn't list it, and I get error #1, file not found.
Bill,
Don't really have an idea. I never encountered it doesn't work yet :
OPEN DATABASE testdata
CREATE SQL VIEW testsqlview as ;
select a.*, b.First_Name, b.Last_Name,c.Company,c.Contact ;
from force orders a ;
inner join employee b on a.emp_id = b.emp_id ;
inner join customer on a.cust_id = c.cust_id
'Not a character expression' is the result of 'force' preventing it to be opened by designer.
Other than that I can use, browse etc or list with gendbc this view. Modify database also shows it as a regular object in dbc.
Cetin