I have no fucking idea of the MS Access SQL dialect, and I'm not really sure I understand what you're looking for, but from a bit of reading the reference, try something like this (backup first!):
Code:
UPDATE TableA SET TableA.ColumnA = (SELECT Max(TableB.ColumnB) FROM TableB)
WHERE (((TableA.ColumnA) Is Null));
Basically, make getting the max of ColumnB using a subquery, so it doesn't try and evaluate the aggregate as part of the outer query.