SQL to VBA not working

[Brilstern]

Not sure what I am missing. Need a second pair of eyes on this. I have an update query that is working fine and will update about 40 rows. When I move this over to VBA... no luck. 0 Records. What did I mess up?

SQL

UPDATE tblTopVulnMachines SET tblTopVulnMachines.OSCPE = "Microsft Windows 7 SP1 x64 Enterprise"
WHERE (((tblTopVulnMachines.OSCPE)="cpe:/o:microso​ft:windows​_7::sp1:x64-enterpr​ise"));

VBA

strSQLUpdate = "UPDATE tblTopVulnMachines SET tblTopVulnMachines.OSCPE = ""Microsft Windows 7 SP1 x64 Enterprise"" WHERE " & _
               "(((tblTopVulnMachines.OSCPE)=""cpe:/o:microsoft:windows_7::sp1:x64-enterprise""));"
 
DoCmd.RunSQL strSQLUpdate

[Rabbit]

Try using single quotes to delineate strings. The standard is single quotes but the access query builder will accept both whereas any other SQL engine usually doesn't.

[Brilstern]

Thanks Rabbit! Unfortunately no change in results. It still isn't matching any records.

[jforbes]

Might try removing all the Parenthesis. You don't need them and Access really gets carried away with them.

[jforbes]

Also, the value that is being Updated is also in the Where Clause, meaning the records will only be updated once. Unless new records are added.

[Brilstern]

No luck with that either JF. I also tried declaring the values in strings and parsing that through the SQL and no change. Not sure what I am missing here.

[Brilstern]

This is a repeated process when information is imported to the database so the occurrence won't be an issue.

[Rabbit]

You said earlier that the update query "will" update 40 rows. Did you let it or did you cancel the update?

[Brilstern]

I did but I have refreshed the data. I Truncate and re-import all my data each time to ensure I have clear data. The values are back to "cpe:/o:microso​ft:windows​_7::sp1:x64-enterpr​ise"

[jforbes]

Other things to try:

• Replace DoCmd.RunSQL with CurrentDB.Execute
• Add a From Clause
• You might want to break the code and debug.print strSQLUpdate. Then you can copy and paste it into a new query and fiddle around with it. Possibly convert it into a Select and see if it returns records.

[Brilstern]

Ok thanks for the troubleshooting suggestions, I'll check those out and hit y'all back.