update table (Access 2000)

Topic

New Reply

I beg a little help with an update query. I have one table called orders. This table has a field called PaymentID. Then i import similar table called Orders1. This table also has a field called Payment.Sometimes the data in the fields Payment are not the same.I want to make an updating so that to make valid only the data from the table orders1. After the updating i want to delete the table Orders1. Then, when next time i again import a table orders1 i must be able the carryout the updating again, and then to delete the table orders1 again.May i have some help in constructung the code. As far as i understand, i must update : update orders.InnerJoin, orders.paymentid to orders1.paymentid, in order to insert the data from orders1 into orders?I aslo want to use the
known code CurrentDb.Execute in order to cary out the updating.
I hope i have made myslef clear what i want.I will be grateful if i receive some help in constructing the code

Reply | Quote

Replies

Hello Aral,

If I understand what you want correctly, you want to update those records in Orders that have the same PaymentID as a record in Orders1, and then delete Orders1. Here is an example of VBA code to do this:

‘ Build and execute SQL statement
Dim strSQL As String
strSQL = “UPDATE Orders INNER JOIN Orders1 ON Orders1.PaymentID = Orders.PaymentID ” & _
“SET Orders.Company = Orders1.Company, Orders.OrderDate = Orders1.OrderDate”
CurrentDb.Execute strSQL, dbFailOnError
‘ Delete Orders1 table
DoCmd.DeleteObject acTable, “Orders1”

In the SQL statement above, you will need to replace the field names I used (Company, Orderdate) by all fields you want to be transferred. You can’t use * in an update query.

HTH, Hans

Reply | Quote