Question:
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.Recordsetrs.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 = 3rs.update
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.
Answer:
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.
