Can't Update ADO Recordset

[gray]

Hi

Access 2007, .mdb file, local machine only

I open an ADO recordset but can't update the fields in it. The .update below:-

Set Prc_RstADO_A = New ADODB.Recordset
Prc_RstADO_A.Open First_Select_Str, CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
Do Until Prc_RstADO_A.EOF
    Prc_RstADO_A![TBL1.Deleted] = Lock_Mode
    Prc_RstADO_A![TBL1.Date_Record_Marked_For_Deletion] = Lock_Date
    Prc_RstADO_A![TBL1.Deleted_By_Unique_No] = Curr_User_Unique_No
    Prc_RstADO_A.Update
    Prc_RstADO_A.MoveNext
Loop

fails with:-

Error No: -2147467259 - Error Decription: Insufficient base table information for updating or refreshing

The RS has a SELECT with two INNER joins in it, hence the TBL1.xxx references. The three tables involved have their own PKey.

The RS also [potentially] has a UNION SELECT in it too....

Here's an example:-

SELECT TBL1.*, TBL2.Item_Name, TBL2.Unique_No, TBL2.Table_Name, TBL2.Deleted, TBL2.Date_Record_Marked_For_Deletion, TBL2.Deleted_By_Unique_No, TBL3.Item_Name, TBL3.Unique_No, TBL3.Table_Name FROM (((Addrs AS TBL1) INNER JOIN Addrs_To_Cntcts_Home AS TBL2 ON TBL1.Unique_No = TBL2.Addrs_Unique_No) INNER JOIN Cntcts AS TBL3 ON TBL3.Unique_No = TBL2.Cntcts_Unique_No) WHERE TBL1.Unique_No IN (790,760,777,782,784) AND TBL1.Deleted=False AND (TBL1.Record_Locked=False AND TBL2.Record_Locked=False)
UNION
SELECT TBL1.*, TBL2.Item_Name, TBL2.Unique_No, TBL2.Table_Name, TBL2.Deleted, TBL2.Date_Record_Marked_For_Deletion, TBL2.Deleted_By_Unique_No, TBL3.Item_Name, TBL3.Unique_No, TBL3.Table_Name FROM (((Addrs AS TBL1) INNER JOIN Addrs_To_Cntcts_Work AS TBL2 ON TBL1.Unique_No = TBL2.Addrs_Unique_No) INNER JOIN Cntcts AS TBL3 ON TBL3.Unique_No = TBL2.Cntcts_Unique_No) WHERE TBL1.Unique_No IN (790,760,777,782,784) AND TBL1.Deleted=False AND (TBL1.Record_Locked=False AND TBL2.Record_Locked=False)
UNION
SELECT TBL1.*, TBL2.Item_Name, TBL2.Unique_No, TBL2.Table_Name, TBL2.Deleted, TBL2.Date_Record_Marked_For_Deletion, TBL2.Deleted_By_Unique_No, TBL3.Item_Name, TBL3.Unique_No, TBL3.Table_Name FROM (((Addrs AS TBL1) INNER JOIN Addrs_Trgs_Premises AS TBL2 ON TBL1.Unique_No = TBL2.Addrs_Unique_No) INNER JOIN Orgs AS TBL3 ON TBL3.Unique_No = TBL2.Orgs_Unique_No) WHERE TBL1.Unique_No IN (790,760,777,782,784) AND TBL1.Deleted=False AND (TBL1.Record_Locked=False AND TBL2.Record_Locked=False)
UNION
SELECT TBL1.*, TBL2.Item_Name, TBL2.Unique_No, TBL2.Table_Name, TBL2.Deleted, TBL2.Date_Record_Marked_For_Deletion, TBL2.Deleted_By_Unique_No, TBL3.Item_Name, TBL3.Unique_No, TBL3.Table_Name FROM (((Addrs AS TBL1) INNER JOIN Addrs_Trgs_Site AS TBL2 ON TBL1.Unique_No = TBL2.Addrs_Unique_No) INNER JOIN Orgs AS TBL3 ON TBL3.Unique_No = TBL2.Orgs_Unique_No) WHERE TBL1.Unique_No IN (790,760,777,782,784) AND TBL1.Deleted=False AND (TBL1.Record_Locked=False AND TBL2.Record_Locked=False)

Are my worst fears true in that this is failing because of the JOINs and/or the UNIONs? I hope not and that it's one of the myriad lock/cursor settings?

Any ideas please? Thanks

 

[Galaxiom]

Union queries and some joins are not updateable.

 

[gray]

Curses! Thanks for the confirmation!