Multiple UPDATEs in one Access SQL query

Question

I'm trying to make a query like so:

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

But when I go to save, Access errors with:

Characters found after end of SQL statement

After some searching, it would appear this is because Access can only do one query at a time.

How can I do this?

Fin

Can you make a VB procedure executing the two statements? – Reichstag

Yes, you can only create/execute one query at a time when using "Create > Query". – Gamecock

Answer 1

Where are you working? You can run multiple queries in VBA or via macros. Some examples:

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

Saved query:

CurrentDb.Execute "Query5", dbFailOnError

Lyly

Answer 2

I found this sample:

MS ACCESS 2007: UPDATE QUERY THAT UPDATES VALUES IN ONE TABLE WITH VALUES FROM ANOTHER TABLE

uses the designer to create the query easily:

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

Custodial