Editable recordsets with outer joins (AC2007) (1 Viewer)

[AOB]

Hi there,

I have a query which I use to populate a recordset (effectively, a subset of a table based on some criteria), which is then looped through and updated (can't avoid the loop - I can't update the table with SQL, it requires VBA code unfortunately)

Stupidly, I forgot that if the query to populate the recordset uses outer joins, the recordset becomes inherently non-updatable. So the loop errors out. :banghead:

Here is/was the original query - the joins are used purely as a criteria for inclusion for the records in the main table which need to be updated; no records in the joined tables should be edited.

SELECT A.field1, A.field2
  FROM (tbl1 A
    LEFT JOIN tbl2 B ON A.field3= B.field3) 
    LEFT JOIN tbl3 C ON A.field3= C.field3
  WHERE B.field3 Is Not Null 
     OR C.field3 Is Not Null 
     OR A.checkflag = True

In order to make the recordset editable, I have to remove the joins like this :

SELECT A.field1, A.field2
   FROM (tbl1 A
     WHERE A.field3 IN (SELECT B.field3 FROM tbl2 B)
      OR A.field3 IN (SELECT C.field3 FROM tbl3 C)
      OR A.checkflag = True

But this makes the query a lot slower.

Just wondering if anybody has any suggestions for a better way to do this?

Cheers!

AOB

 

[CJ_London]

with your original query have you tried changing the form recordsource type to dynaset - inconsistent updates

not sure if it will be faster but another variation would be

 SELECT A.field1, A.field2
   FROM (tbl1 A
     WHERE Exists(SELECT * FROM tbl2 WHERE field3=a.field3)
      OR Exists(SELECT * FROM tbl3 WHERE field3=a.field3)
      OR A.checkflag = True

And of course - I presume all field3's are indexed, if they are not and it is a large dataset, expect extremely poor performance.

 

[AOB]

They are indexed, yes (of course ) - dataset not massive (~50k records?)

If I change the recordset type to dynaset, does that impact the fact I am updating records within it (nod to "inconsistent updates") ?

 

[CJ_London]

If I change the recordset type to dynaset, does that impact the fact I am updating records within it (nod to "inconsistent updates") ?

the default is dynaset so not quite sure what you are asking. See this link

https://msdn.microsoft.com/en-us/library/office/ff821178.aspx

Note that the fact that the fields becomes editable does not mean they can be edited/updated - if query is a group by for example, it will never be editable because there is no record identifier. Similarly with left joins, you cannot edit a null field on the child side of the join because the record does not exist to be edited in the first place.

My question about indexing - you would be surprised how many people do not index their tables beyond the primary key (not even the family key) and your query would not appear to be using either