Deleting rows in joined tables using ADO

Question

Now I have seen this question in another forum but it didn't had an acceptable answer.

Suppose I have two tables, the Groups table and the Elements table. The tables have no defined relationships. The Elements table has an IdGroup field that refers to the IdGroup (PK) field of the Groups table.

I use the following query through an ADO recordset to populate the tables values to a datagrid:

SELECT Elements.*, Groups.GroupName
FROM Elements
INNER JOIN Groups ON Elements.IdGroup = Groups.IdGroup

From that grid I want to press Delete in order to delete an Element. Here is my problem. When I used DAO, the DAO Delete() function deleted only the record in the Elements group. This was the expected behavior.

When I changed to ADO, the Delete() function deleted records in both tables, the element record and the group to which the element belonged!

Is there any way to reproduce the DAO behavior in ADO without having to define relationships into the tables?

Note: I know there are alternatives (executing DELETE querys could do the job). Just show me a way to do this in ADO, or say it cannot be done.

Answer 1

Rewrite you query to:

• replace the INNER JOIN with a WHERE clause consisting of an EXISTS;
• use a subquery in the SELECT clause to return the value of Groups.GroupName.

Example:

SELECT Elements.*, 
       (
        SELECT Groups.GroupName
          FROM Groups 
         WHERE Elements.IdGroup = Groups.IdGroup
       )
  FROM Elements
 WHERE EXISTS (
               SELECT * 
                 FROM Groups 
                WHERE Elements.IdGroup = Groups.IdGroup
              );

I've tested this using SQL Server 2008 with a ADO recordset set as the DataSource property of a Microsoft OLEDB Datagrid Control (MSDATGRD.OCX) then deleting the row via the gird (I assume you are doing something similar) and the row is indeed deleted from table Elements only (i.e. the row in Groups remains undeleted).

Note the revised query may have a negative impact on performance when fetching rows.