Dim strSQL As String, ss As String

ss = "hgg"  ' Вариант строки 1
ss = "123"  ' Вариант строки 2 

strSQL = "UPDATE tbl1 SET tbl1.txt = " & """hgg""" & " WHERE (((tbl1.COD)=2));" ' Работает
strSQL = "UPDATE tbl1 SET tbl1.txt = " & ss & " WHERE (((tbl1.COD)=2));"  ' Работает только с вариантом 2

DoCmd.RunSQL strSQL 

ss = "xx"
gTxt = ss
strSQL = "UPDATE tbl1 SET tbl1.txt = fTxt()" & _
         "WHERE (((tbl1.COD)=2));"

Public gTxt As String
Public Function fTxt() As String
fTxt = gTxt
End Function