Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Beginner
Jun 20, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date