The first thing I noticed was that on the SET line of your query, you use a dot on one side and a bang/exclamation on the other. Unless you're actually referencing an object outside the Query itself (for example, a value on a Form), you should always try and use a dot.
I also noticed that there are no spaces at the end/beginning of each line. This means that, once Access has put everything together, you end up with the following: UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOCSET tbl2.Name = tbl1!NameWHERE (((tbl2.Name) Is Null));" (notice how tbl2.LOC and SET are combined into one word and how tbl1!Name and WHERE are in the same situation.
Try the following and see if it makes any difference.
Code:
sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC " & _
"SET tbl2.Name = tbl1.Name " & _
"WHERE (((tbl2.Name) Is Null));"