I am using VB6 to execute an sql statement. I have two access tables and want to insert the contents of a field in one into the other subject to the key no being the same. I have been unable to get it to work and would appreciate some help. I have:

update mytable set mytable.fieldx = othertable.fieldx where mytable.keyno = othertable.keyno

Any offers please?
thanks

In order to reference two tables you have to join them. Try

UPDATE mytable
   SET mytable.fieldx = othertable.fieldx
  FROM mytable1 INNER JOIN othertable
    ON mytable.keyno = othertable.keyno

or maybe clearer using aliases

UPDATE m
   SET m.fieldx = o.fieldx
  FROM mytable1 m INNER JOIN othertable o
    ON m.keyno = o.keyno
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.