Multiple UPDATE in a single SQL Access query

Question

I am trying to make a query like this:

UPDATE table1 SET col1 = 'foo', col2 = 'bar'; UPDATE table2 SET hi = 'bye', bye = 'hi'; 

But when I go to save, access errors using:

Characters Found After Completing an SQL Statement

After some searching, this will look because Access can only perform one request at a time.

How can i do this?

Answer 1

Where do you work? You can run multiple queries in VBA or through macros. Some examples:

 CurrentDB.Execute "UPDATE table1 SET col1 = 'foo', col2 = 'bar';", dbFailOnError CurrentDB.Execute "UPDATE table2 SET hi = 'bye', bye = 'hi';", dbFailOnError 

Saved request:

 CurrentDb.Execute "Query5", dbFailOnError 

Answer 2

I found this sample:

MS ACCESS 2007: UPDATE THE QUESTION WHICH UPDATES VALUES IN ONE TABLE WITH VALUES FROM OTHER TABLE

uses the constructor to easily create a query:

 UPDATE Big INNER JOIN Bot ON Big.PART = Bot.PART SET Bot.MFG = [Big].[MFG];