Mi era sfuggito che usi un database MDB (Access).

A parte questo, la query è in ogni caso sbagliata perchè è necessario usare un'alias nella SELECT interna, per cui dovrebbe essere:

codice:
UPDATE nometabella
SET area= (SELECT SUM(b.area) FROM nometabella b) 
WHERE nome='TOT'
Mentre in SQLServer funziona perfettamente, anche se è necessario fare il CONVERT sul campo AREA in quanto tutti i campi sono definiti di testo (cosa che non ha alcun senso!)

codice:
UPDATE nometabella
SET area= (SELECT SUM(CONVERT(Int, b.area)) FROM nometabella b) 
WHERE nome='TOT'
invece in un MDB che ha dei limiti non sembra possibile eseguirla, almeno io non ci sono riuscito nemmeno ricreando la tabella usando i tipi corretti per i campi.
Per cui mi sa che devi 'incartartela' ed aggirare il problema, ovvero:

1. prima devi eseguire la SELECT per ottenere il valore dell'area
codice:
Dim rs As ADODB.Recordset
Set rs = CN.Execute("SELECT SUM(area) As Totale FROM tabella")
(verifica che ti restituisca il recordset con il valore corretto)

2. poi aggiornare il valore
codice:
CN.Execute "UPDATE nometabella SET area=" & rs("Totale).Value & " WHERE nome = 'TOT'"