The command you are using appears to be correct. I suspect the tables it is failing on are already using that table style so the local overrides are not be wiped. What happens if you first move away from the preferred table style and then apply the right one?
Code:
Sub ApplyTableStyle()
Dim t As Table
For Each t In ActiveDocument.Tables
t.Style = "Table Normal"
t.Style = "Light Shading - Accent 3"
Next
End Sub