Results 1 to 2 of 2
2002-11-24, 10:42 #1
- 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) "
2002-11-24, 20:40 #2
- Join Date
- Dec 2001
- Melbourne, Australia
- 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>