Syntax error in Left Join in vb.net on access data base

Question

The following code in vb.net 2010 express generates this error:

syntax error (missing operator) in query expression ''.

sql = "SELECT Machines.LocationID FROM Lanes LEFT JOIN Cabinets ON
Lanes.CabinetID=Cabinets.ID AND Cabinets.Name IS NOT NULL LEFT JOIN Machines ON
Cabinets.MachineID=Machines.ID "

Dim mAdapter As New OleDbDataAdapter(sql, con)
Dim mt As New DataTable("Results")
mAdapter.Fill(mt)

con is defined in a module of the project as:

Public myConString As String = "Provider=Microsoft.ACE.OLEDB.12.0"
Public databaseName As String = "C:\vending.mdb"
Public con As New OleDb.OleDbConnection(myConString & ";Data Source =" & databaseName)

If I comment out the second Left Join command, the statement executes properly. This exact same sql statement when executed from SQL Server works fine.

Tables are defined as:

Locations

ID INTEGER IDENTITY
Company
Name
...

Machines

ID INTEGER IDENTITY
LocationID Integer
Name as Text(100)
SerialNum as Text(100)
...

Cabinets

ID INTEGER IDENTITY
Name TEXT(100)
MachineID INTEGER
...

Lanes

ID INTEGER IDENTITY
Name TEXT(100)
CabinetID INTEGER
...

If I add parentheses around the join statements like so:

sql = "SELECT Machines.LocationID FROM ((Lanes LEFT JOIN Cabinets ON
Lanes.CabinetID=Cabinets.ID AND Cabinets.Name IS NOT NULL) LEFT JOIN Machines ON 
Cabinets.MachineID=Machines.ID) "

I get the error:

Join expression not supported.

PLEASE HELP!

Good question. I've got the same kinds of problems and I could really use some comprehensive list of differences between T-SQL and Access SQL; does anyone know of something like that? — Mr Lister

HansUp HansUp · Accepted Answer · 2013-04-28T14:15:19

Access requires parentheses in the FROM clause for any query which includes more that one JOIN.

Separate from that issue, I think you'll also need another pair of parentheses around that compound ON clause (the first of the two ON clauses).

This untested Access SQL looks correct to me, but I think you should build your own using Access' query designer if possible. The designer knows about the join syntax which keeps the db engine happy, and will automatically add the required parentheses for you.

SELECT Machines.LocationID
FROM
    (Lanes
    LEFT JOIN Cabinets
    ON (Lanes.CabinetID=Cabinets.ID AND Cabinets.Name IS NOT NULL))
    LEFT JOIN Machines
    ON Cabinets.MachineID=Machines.ID

Name is a reserved word, and prefixing it with the table name or alias is one technique to prevent the reserved word from confusing the db engine. Enclosing the field name in square brackets is another. You can even combine both if you wish ... table_name.[reserved_word] ... for a "belts and suspenders" approach.

Syntax error in Left Join in vb.net on access data base

1 Answers