0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
Whiteclaws
  • 902
  • 10
  • 31
  • 1
    Why do that as 19 `UPDATE` statements, when you could do it as 1? – Thom A Nov 19 '19 at 20:52
  • @Larnu well this is not a good example, but those statements are automatically generated, and might update fields that are not in that table... The idea was to batch those statements instead of updating 200 times on VBA which would be excruciatingly slow. – Whiteclaws Nov 19 '19 at 20:55
  • 1
    with Currentdb.Execute you need to include the DBFailOnError clause – Doug Coats Nov 19 '19 at 21:19
  • 1
    Post your actual execution code. – Krish Nov 19 '19 at 22:20
  • 1
    Are you aware of https://stackoverflow.com/questions/58832269/getting-error-3340-query-is-corrupt-while-executing-queries-docmd-runsql ? (Not sure this is a duplicate too.) – Andre Nov 19 '19 at 22:39
  • @krish KM `currentDb.Execute SQLQuery` is all there is. And SQLQuery is essentially what was given at the top. – Whiteclaws Nov 20 '19 at 05:06
  • 1
    @Whiteclaws reason why we ask to see your code is if you have any other stupid code somewhere that is preventing you from seeing errors i.e. `on error resume next` :) – Krish Nov 20 '19 at 14:26
  • As far as I remember, DoCmd.RunSQL and CurrentDb.Execute only can handle one SQL statement at a time. – Christoph Jüngling Nov 21 '19 at 13:54

1 Answers1

2

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.

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
  • Really? You can't batch SQL statements in Access SQL? Another reason to hate Access, I guess :) . I guess what I feared came true, huh. Also, update statements should be the same between Access SQL and T-SQL – Whiteclaws Nov 21 '19 at 15:33