CODEWITHSUNDEEP
sql-servervbat-sqlms-access
Whiteclaws
Whiteclaws

Reputation: 952

Access 2016 DoCmd.RunSQL & CurrentDb.Execute are not working for update queries

I have a SQLQuery string that I pass to DoCmd.RunSQL or CurrentDb.Execute and both commands return no warnings and don't do anything...

UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-202' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2019-525' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2019-103' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-605' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-520' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-512' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-402' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Tangerine' WHERE [Reference Number] = '2018-203' AND ([Colour] IS NULL OR [Colour] <> 'Tangerine');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2017-609' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2016-616' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2016-528' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2016-524' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2016-405' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2015-206' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2015-204' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2013-530' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2013-509' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2013-208' AND ([Colour] IS NULL OR [Colour] <> 'Orange');
UPDATE [tblEngrDetails] SET [Colour] = 'Orange' WHERE [Reference Number] = '2012-604' AND ([Colour] IS NULL OR [Colour] <> 'Orange');

The query works fine when ran in SQL Server Studio, so I don't know what's wrong.

Upvotes: 0

Views: 454

Answers (1)

SunKnight0
SunKnight0

Reputation: 3351

Are you passing it as a single string to be executed all at once? That will not work. You have to execute each UPDATE query separately.

Also (this is not the case here, but FYI) MS SQL Server SQL is not the same as MS Access SQL. Just because code works on one it does not mean it will work on the other.

Upvotes: 2

Related Questions