Your example doesn't need a join, try:

Set dbs = Workspaces(0).OpenDatabase(App.Path & "\ONE.mdb")
strSql = "UPDATE Table_1 SET SomeValue = 1 WHERE SSN= '555555555' and SSN IN (select distinct SSN from table2)"
dbs.Execute strSql
dbs.Close

1) What you want is a "View". Access, for every release prior to Access 2000 (and I don't know about 2000), does not support views.

2) SQL server does support views but to update data, you need to make a pass for each table that is being updated. In your example, if you wanted to update table1 and table2, you would have to issue two update statements.