Using more than one join in a query

[Bert]

Hello,

I'm currently updating an application and I'm having a SQL problem
which is getting quite urgent. It's Access 97 front-end with a SQL
Server 2000 back-end.

As part of this particular update, I need to run an INSERT query
against a table in the SQL server database. I've done this many times
so far in this update but this is the first time where I've needed to
use two JOINS so I can only assume this is the problem.

The syntax (as used many times previously) of the line in the VBA code
causing the error is:-

dbsql.execute updSQL

dbsql being a database object which links to the SQL Server database
and UpdSQL being a string which contains the following:-

-------

INSERT INTO Holdings_Rent_Payment_Level
(Property_Lease_ID,Property_Lease_Pay_Dt,Property_Lease_Rent_Pay_Period_From,
Property_Lease_Rent_Pay_Period_To,
Property_Lease_Rent_Pay_Amount_Sterling,Property_Lease_Rent_Pay_Amount_Euro,
Property_Lease_Rent_Pay_Notes)

SELECT NewLease.Property_Lease_ID, Src.Property_Lease_Pay_Dt,
Src.Property_Lease_Rent_Pay_Period_From,
Src.Property_Lease_Rent_Pay_Period_To,
Src.Property_Lease_Rent_Pay_Amount_Sterling,
Src.Property_Lease_Rent_Pay_Amount_Euro,
Src.Property_Lease_Rent_Pay_Notes

FROM Holdings_Rent_Payment_Level src

INNER JOIN Holdings_Property_Lease_Level
ON Holdings_Property_Lease_Level.Property_Lease_ID =
src.Property_Lease_ID

INNER JOIN Holdings_Property_Lease_Level NewLease
ON NewLease.Holdings_ID = 3665

WHERE Holdings_Property_Lease_Level.Holdings_ID = 1816

---------

(The SQL is generated by a function, hence the presence of what appear
to be hard-coded UIDs)

The error returned when trying to execute this line is:-

"Syntax error (missing operator) in query expression" - followed by an
excerpt from the SQL query.

I've read a few topics which explain about how access needs the joins
to be bracketed in order for it to know the order in which to execute
them, but no matter what combination of brackets I try, it simply will
not work. When using the brackets I get a "Join Expression Not
Supported" error.

Any help would be very much appreciated!

Thanks,

Rob

 

[John Spencer]

When you use more than two tables in the FROM clause Access insists on
parentheses being included to determine the joins. Also, as far as I recall
Access97 will not support the type of join you have in this phrase
INNER JOIN Holdings_Property_Lease_Level NewLease ON NewLease.Holdings_ID
= 3665

You can either try making this a pass-through query or build the query
differently.

Perhaps building the FROM clause so it looks like the following MAY work for
you.

FROM (Holdings_Rent_Payment_Level src

INNER JOIN Holdings_Property_Lease_Level
ON Holdings_Property_Lease_Level.Property_Lease_ID =
src.Property_Lease_ID) INNER JOIN
Holdings_Property_Lease_Level NewLease ON
NewLease.Holdings_ID = Holdings_Property_Lease_Level.Holdings_ID

WHERE Holdings_Property_Lease_Level.Holdings_ID = 3665