with your connection object use

strSQl = "SELECT * FROM unit, unittype, estate WHERE untestateidno = estateid AND unttypeidno = unittypeid"

Set rstUnit = connObject.execute(strSQL)

for example I use a dataenvironment called DataEnv with a connection called dbTSS so I would use

dim rstMine as adodb.recordset
set rstMine = dataenv.dbTss.execute(strSQL)

And it is that easy! :)