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

    update table (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: update table (Access 2000)

    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

Posting Permissions

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