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.RegionName
FROM District INNER JOIN Region
ON District.RegionId = RegionId", cn
Now, with this recordset, it gives an "Insufficient key column information..." message on rs.Delete method. Moreover, when I update the value RegionId like :
rs!RegionId = 3
The value of 'RegionName' should be updated correspondingly. But it doesn't happen. It will only be updated when I re-query it. But it will be unnecessary extra work and more importantly, the record pointer will be changed on re-query.
So could you give me solution to both update and delete problems. Please note that, the same query works well in Access.
Generally you can't update records in or delete records from a joined recordset. The UPDATE and DELETE SQL statements use a single table only. The exception is when you're dealing with a VIEW in database systems like Oracle or SQL Server. Access won't allow you to do what you need to accomplish. You should update each table individually or delete records from both tables as you need to.