Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Thanked 0 Times in 0 Posts

    Update rows through deleting (Access 2000)

    Udpate rows through deleting

    I am afraid i do not epxress myself properly.Anyway my problem is: I have a function called Collect, for appending all the orders from table Orders1 into the
    table Orders. The two tables have identical structure,they have the same rows. This function performs well,and apends all the details for all the new orders.I have the following problem.Sometimes some of the orders are deleted from the table Orders1, but my function is not instructed to delete them too, and therefore in the end i have orders in the table Orders that should not be there,that should be deleted and not existing.So my question is, how can i give a command before appending, to delete all the orders from the table Orders that are not present in the Table Orders1?

    My function Collect is the following:

    Public Function Collect()

    Dim StrAppendOrders As String

    StrAppendOrders = "INSERT INTO orders (OrderID, CustomerID, OrderDate, paymentid, PaymentMethodID, bankid, invoicedate, AuftragNr) " & _
    "SELECT o1.orderid, o1.customerid, o1.orderdate, o1.paymentid, o1.PaymentMethodID, o1.bankid, o1.invoicedate, o1.AuftragNr " & _
    " FROM orders1 As o1 WHERE NOT EXISTS (SELECT * FROM Orders WHERE OrderID=o1.OrderID) "
    CurrentDb.Execute StrAppendOrders

    End Function

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 32 Times in 32 Posts

    Re: Update rows through deleting (Access 2000)

    An easier way (and safer as far as data goes) is to introduce a Yes/No field for the deleted records. In this way you never delete records.
    Why do you want to keep two tables with the same detail in them?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts