1

A simple UPDATE query with INNER JOIN is causing me problems, using VB6 and DAO on an Access .MDB database.

I thought it would be simple, but whatever changes I make generate errors.

My query is:

UPDATE work
INNER JOIN emp ON work.ref = emp.ref 
SET work.code1 = emp.code1

This generates run-time error 3075 Syntax error in query expression 'work.ref = emp.ref'.

I get a similar error with:

UPDATE work w
INNER JOIN emp ON w.ref = emp.ref 
SET w.code1 = emp.code1

and

UPDATE [work] w
INNER JOIN emp ON w.ref = emp.ref 
SET w.code1 = emp.code1

and also if I use alias e for table emp.

I cannot use a FROM clause, which is not supported in Access (Thanks though @MarkKram)

I have to use DAO 3.51 (old!) in VB6, which cannot easily be changed.

I have tried square brackets around the first reference to work as in UPDATE [work] (as I need this in a simple SELECT * FROM [work]) as well as various combinations with and without square brackets around table names and column names. But still it fails.

Do you have any suggestions please?

Dim ws As DAO.Workspace
Dim DB As DAO.Database
Dim szSQL As String

Set ws = gWS
Set DB = gWS.OpenDatabase(WorkFile)

szSQL = "UPDATE work INNER JOIN emp ON work.ref = emp.ref SET work.code1 = emp.code1 WHERE work.trancode = 'P'"
DB.Execute szSQL

Set DB = Nothing
Set ws = Nothing
finch
  • 549
  • 1
  • 6
  • 18
  • Try this instead:. UPDATE w SET w.code1 = e.code1 from work w INNER JOIN emp e ON w.ref = e.ref w.trancode = 'P' – Mark Kram Nov 25 '17 at 17:59
  • @MarkKram [Access syntax](https://stackoverflow.com/a/379242/11683) is different. – GSerg Nov 25 '17 at 18:01
  • Take a look at this link then:https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join I really don't use Access very much – Mark Kram Nov 25 '17 at 18:03
  • Your query is fine if saved in Access or executed directly against it. If you have a pass-through situation, you will need to use a more ANSI version of SQL (`UPDATE ... SET ... FROM ... INNER JOIN ... WHERE...`). – GSerg Nov 25 '17 at 18:06
  • @Gserg, as I have to use DAO 3.51 - equivalent to Access 97 - more recent Access syntax may not work. – finch Nov 25 '17 at 19:01
  • 1
    Try this: `UPDATE work, emp SET work.code1 = emp.code1 WHERE work.ref = emp.ref AND work.transcode ='P'` – Rene Nov 25 '17 at 19:59
  • Wow, @Rene you are a star. Your suggestion worked a dream - just as I was about to give up. Please post your suggestion as an answer , so that I can accept it. This will save me so much work, so many thanks! :) – finch Nov 25 '17 at 20:26
  • To the person who gave my question a downvote, I would love to know why, so that I can improve next time. As somebody has been able to come up with a solution within a few hours, surely it was not that bad? – finch Nov 25 '17 at 20:28
  • Often dumb downvoters just don't understand the topic of the question. – Gustav Nov 25 '17 at 21:01
  • This question assumes that the problem had something to do with VB6, DAO, or Access, when the issue was SQL syntax – StayOnTarget Nov 27 '17 at 12:32
  • @DaveInCaz The query is not a general SQL query but specific to Access/DAO. I think also the version/platform makes a difference, as the problem query did not work in DAO 3.51, but worked in [MDB Viewer Plus](http://www.alexnolan.net/software/mdb_viewer_plus.htm), which presumably has a different data access mechanism. – finch Nov 28 '17 at 12:52

1 Answers1

3

Try this: 

UPDATE work, emp 
SET work.code1 = emp.code1 
WHERE work.ref = emp.ref AND work.transcode ='P'
Rene
  • 1,095
  • 1
  • 8
  • 17