CODEWITHSUNDEEP
sqlms-accessms-access-2007
Danny Beckett
Danny Beckett

Reputation: 20748

Multiple UPDATEs in one Access SQL query

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?

Upvotes: 6

Views: 23546

Answers (2)

Sohail
Sohail

Reputation: 413

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

Upvotes: -1

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 5

Related Questions