Problem with Multiple tables

When I open an ADO recordset with JOINs, I couldn't delete (with .delete method) records in the primary table. It gives "Insufficient Column .....". Moreover, on updating value of the key field, the values of the other fields of the secondary table would not be refreshed.

Could anyone give the solution to these problems?

Duplicate your query in Access, SQL, whatever and see if you can update it there. Chances are that your query doesn't include enough fields from each table to make it updatable or the join isn't properly formed. With MS flavors of SQL you can only update a query that contains multiple table if you have the joins done right (using the INNER/OUTER join statements). I've seen some people try to simulate joins by simply equating a PK to an FK (kinda like Oracle does joins) will produce a 'read only' recordset.

Good luck,
Paul

Thank you for your suggestion. I tried several ways but couldn't solve the problem. The same SQL works fine in Access, but even on copying the same SQL it doesn't work with Visual Basic. Let me put the problem with one simple example.

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 "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 oupdated when I requery it. But it will be unnecessary extra work and more importantly, the record pointer will be changed on requery.

So could anyone give me solution to both update and delete problems. Please note that, the same query works well in Access.

You should replace:
Dim rs as new ADODB.Recordset
rs.Open "SELECT District.*, Region.RegionName FROM District INNER JOIN Region ON District.RegionId = RegionId", cn


with:
Dim rs as new ADODB.Recordset
rs.Open "SELECT District.*, Region.RegionName FROM District INNER JOIN Region ON District.RegionId = Region.RegionId", cn

** Since there are two RegionIDs available to the query at this point, you need to specify what table it is in. At this point, the fields available to your query are not limited to the fields you have in your SELECT (fieldlist) portion of your query.

Hope this helps.

Doug Davison
ddavison@horizonhobby.com
Horizon Hobby, Inc.