Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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

DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap