MS ACCESS SQL Join Syntax

Question

I can get this query to run if I remove the Join, but once I add the join, I get the following error:

Run-Time error '3135': Syntax error in Join Operation

sourceDB = "C:\sourcedb.accdb"

SQL = "SELECT e1.lid " & _
        "FROM (eventlog e1 IN  '" & sourceDB & "'" & _
        "LEFT JOIN eventlog e2 ON e2.lid = e1.lid)"

Any advice on what I might be doing wrong

Try as e1 and as e2. As far as I know, MS Access requires the as for table aliases. — Gordon Linoff
– Gordon Linoff, Commented May 31, 2015 at 23:16

ChrisOG · Accepted Answer · 2015-06-07 20:39:04Z

2

Try changing your code to the following:

sourceDB = "C:\sourcedb.accdb"

SQL = "SELECT e1.lid " & _
      "FROM [" & sourceDB & "].[eventlog] AS e1 " & _
      "LEFT JOIN eventlog AS e2 ON e2.lid = e1.lid"

answered Jun 7, 2015 at 20:39

ChrisOG

362 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thomasw_lrd Over a year ago

That was the answer I was looking. I had my join all wrong.

Alexander Bell · Accepted Answer · 2015-05-31 22:35:34Z

0

You have an obvious syntax error in your query: the join must be between two Tables, not the Table and Database name. Take a look at this canonical example (re: https://msdn.microsoft.com/en-us/library/office/ff198084.aspx) and correct your query correspondingly:

SELECT CategoryName, ProductName FROM Categories LEFT JOIN Products 
ON Categories.CategoryID = Products.CategoryID;

Hope this will help. Best regards,

edited May 31, 2015 at 22:35

answered May 31, 2015 at 22:31

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

3 Comments

thomasw_lrd Over a year ago

That makes sense, but the join is between eventlog 1 and eventlog2. Unless the IN statement is the wrong part?

thomasw_lrd Over a year ago

And my IN stament was in the wrong spot. Thanks for the help.

Alexander Bell Over a year ago

I have extended the answer. If you are satisfied with the answer please mark it accepted. Good luck with your project. Best regards,

Collectives™ on Stack Overflow

MS ACCESS SQL Join Syntax

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related