Joined-Sql Updates/Deletes using updateBatch

From: Jen (anonymous_at_discussions.microsoft.com)
Date: 05/24/04 

Date: Mon, 24 May 2004 11:56:04 -0700

Hi,

I have 2 tables, One is the trasaction table the other is a lookup table. My sql statement is as follows -

SELECT T.Fld1, T.Fld2, L.Fld2, T.fld3
FROM Transaction T, Lookup L
WHERE T.PK = L.PK

I create the recordset using the above query and persist it as a dom. My app updates some values in the dom and may also delete a record from the Trasaction table. When I try to update my database, using this dom i.e. -

With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        
        .open Source:=dom

        Set .ActiveConnection = ctn

        .UpdateBatch
End With

It also tries to update the lookup table and it fails. If the transaction record is deleted, it tries to delete the record from the lookup table also.

Is there anyway to avoid this. Please Help. Thanks in advance.

Relevant Pages

  • Re: Joined-Sql Updates/Deletes using updateBatch
    ... One is the trasaction table the other is a lookup table. ... My> app updates some values in the dom and may also delete a record from the> Trasaction table. ... > .CursorLocation = adUseClient ... If the transaction> record is deleted, it tries to delete the record from the lookup table> also. ...
    (microsoft.public.data.ado)
  • "Condition table" query
    ... I have a reference table 'LOOKUP' with 4 fields and sample values as ... Plant Group WorkCentre WageType CalcType ... I need a query which will return all records in the transaction table ...
    (comp.sys.ibm.as400.misc)
  • RE: Way around LEFT OUTER JOIN?
    ... The transaction table, in my original post, will have thousands of rows. ... BTW, I probably should have my clustered PK on the transaction table, and not this lookup table. ...
    (microsoft.public.sqlserver.programming)
  • Re: "Condition table" query
    ... which find a match for a given CalcType in the Lookup table, ... which find any match for a given CalcType in the lookup table? ... I constrained to use procedural logic, examining each transaction ...     and l.group in ...
    (comp.sys.ibm.as400.misc)