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?

Yes, you can only create/execute one query at a time when using "Create > Query". — Krisztián Balla, Jan 08 '14 at 13:04

Fionnuala · Accepted Answer · 2012-09-25T11:34:34.417

5

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

edited Sep 25 '12 at 11:34

answered Sep 25 '12 at 11:29

Fionnuala

90,370
7
114
152

score -1 · Answer 2 · answered Jun 16 '15 at 13:21

-1

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];

answered Jun 16 '15 at 13:21

Sohail

403
4
16

Multiple UPDATEs in one Access SQL query

2 Answers2