Access SQL requires the use of brackets in the FROM clause when there are more than 2 tables. Also JOIN is INNER JOIN.
And you can't LEFT JOIN from 2 tables to 1 without additional sub queries.
This works but does not produce a result because there are no records where direction is null.
Code:
SELECT Count(R.E1_Unique_ID) AS CommonNeighbours, R.E1_Unique_ID, R.E2_Unique_ID, R.E1_NAME, R.E2_NAME
FROM (SELECT DISTINCT E1.Unique_ID AS E1_Unique_ID, E2.Unique_ID AS E2_Unique_ID, E1.LastName AS E1_NAME, E2.LastName AS E2_NAME, LE1.Entity_ID2 AS LE1_Entity_ID2
FROM ((Person AS E1 INNER JOIN LinkTable AS LE1 ON E1.Unique_ID = LE1.Entity_ID1) INNER JOIN LinkTable AS LE2 ON LE1.Entity_ID2 = LE2.Entity_ID1) INNER JOIN Person AS E2 ON LE2.Entity_ID2 = E2.Unique_ID
WHERE (((E1.Unique_ID)>[E2].[Unique_Id]))) AS R
LEFT JOIN LinkTable AS LE3 ON (R.LE1_Entity_ID2 = LE3.Entity_ID2) AND (R.E1_Unique_ID = LE3.Entity_ID1)
WHERE (((R.E1_Unique_ID)>[LE1_Entity_ID2]) AND ((LE3.Direction) Is Null))
GROUP BY R.E1_Unique_ID, R.E2_Unique_ID, R.E1_NAME, R.E2_NAME
HAVING (((Count(R.E1_Unique_ID))>=3))
ORDER BY Count(R.E1_Unique_ID) DESC
If the direction criteria is ignored, one result is returned from your example data. Sadly the data in your db does not match the data in your posted example so I can't verify if this actually the required result