devxlogo

Problem with Multiple Table Joins

Problem with Multiple Table Joins

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.

See also  5 Tips for Choosing the Right Medical Software for Your Practice
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist