Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    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
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 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
  •