Problem with Multiple table Joins
--------------------------------------------------------------------------------
I have two tables Region(RegionId-PK, RegionName) and District(DistrictId-PK, DistrictName, RegionId-FK).
When I open a recordset (rs) like :
Dim rs as new ADODB.Recordset
rs.Open "SELECT District.*, Region.* FROM District INNER JOIN Region ON District.RegionId = RegionId", cn
Now, with this recordset, rs.delete method deletes records from both tables. But I need to delete the record from District table (many side) only.
Moreover, when I update the value RegionId like :
rs!RegionId = 3
rs.update
The value of 'RegionName' should be updated correspondingly. But it doesn't happen. It will only be oupdated when I requery it. But it will be unnecessary extra work and more importantly, the record pointer will be changed on requery.
So could anyone give me solution to both update and delete problems. Please note that, the same query works well in Access.
