Are multiple JOINs unavailable when using ADODB to query an Excel file in a VBA procedure?

Question

I have 3 sheets with data formatted as tables. The sheets names are "Riesgos", "Eventos" and "EventosRiesgos".

EventosRiesgo has information relating events and risks (many to many relationship).

I'm trying to get all the risks from Riesgos, but also the events related to the risks from (. I'm using ADODB to query the sheets as database tables and using the following SQL:

SELECT * FROM [Riesgos$] r
LEFT JOIN [EventosRiesgos$] er ON r.[Id]=er.[Id Riesgo]
LEFT JOIN [Eventos$] e ON er.[Id Evento]=e.[Id]

But i get an error, it's in Spanish but a rough translation would be: Syntax error (missing operator) in expression "r.[Id]=er.[Id Riesgo] LEFT JOIN [Eventos$] e ON er.[Id Evento]=e.[Id]"

When I run the query only using the first 2 lines (only one join) everything works as expected. My question is: Why is the query not working when I use the two JOINs?

Can anyone help me, at least to find documentation on the use of ADODB to query Excel sheets?

You can use pretty nearly anything from these : Fundamental Microsoft Jet SQL for Access 2000 -- Intermediate Microsoft Jet SQL for Access 2000 -- Advanced Microsoft Jet SQL for Access 2000 — Fionnuala
– Fionnuala, Commented Sep 7, 2012 at 18:39
Apparently FULL JOINs are not supported, can anyone confirm this? — pablete
– pablete, Commented Sep 12, 2012 at 20:40

SeanC · Accepted Answer · 2012-09-07 18:54:12Z

9

~~Access~~ Jet/ADODB/ACE likes brackets with multiple tables

SELECT  * FROM ( [Riesgos$] r
LEFT JOIN [EventosRiesgos$] er ON r.[Id]=er.[Id Riesgo] )
LEFT JOIN [Eventos$] e ON er.[Id Evento]=e.[Id]

The only difference is brackets around the [riesgos$] r .... er.[Id Riesggo]

edited Sep 7, 2012 at 18:54

answered Sep 7, 2012 at 17:56

SeanC

16k5 gold badges48 silver badges67 bronze badges

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

2 Comments

Stef Heyenrath Over a year ago

It seems that the maximum number of left joins is 8, can somebody confirm / explain this ?

SeanC Over a year ago

@stef, 2007 16, 2010 16, but both say may be less if the query includes multivalued lookup fields.

Collectives™ on Stack Overflow

Are multiple JOINs unavailable when using ADODB to query an Excel file in a VBA procedure?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related