Join Expression Not Supported Error in Access

[Tony K]

Here is another SQL 2005 statement converted to Access SQL that doesn't
work. Error message is: Join Expression Not Supported. I don't know Access
well enough to know what is not supported. (INNER JOIN)?? (LEFT OUTER
JOIN)?? Any ideas??

*****************ORIGINAL SQL 2005 STATEMENT****************
SELECT dbo.inventory_request.inventory_request_id,
dbo.inventory_request.request_number, dbo.inventory_request.creation_date,
dbo.inventory_request.last_modified_date,
dbo.inventory_request.due_date, dbo.inventory_request.ship_date,
dbo.inventory_request.request_status,
dbo.inventory_request.customer_id,
dbo.inventory_request.carrier, dbo.inventory_request.tracking_number,
dbo.inventory_request.is_canceled,
dbo.inventory_request.record_status,
dbo.inventory_request.line_item_count, dbo.inventory_request.parent_id,
dbo.inventory_request.backorder_id,
dbo.inventory_request.is_printed, location_1.code
AS stage_location, sites_1.site_name AS stage_site,
dbo.vIRCombo.item_number,
dbo.vIRCombo.description,
dbo.vIRCombo.request_quantity, dbo.vIRCombo.note_text,
dbo.vIRCombo.picked_quantity, dbo.vIRCombo.shipped_quantity,
dbo.vIRCombo.line_item_id, dbo.vIRCombo.cost
FROM dbo.vIRCombo INNER JOIN
dbo.inventory_request ON
dbo.vIRCombo.inventory_request_id =
dbo.inventory_request.inventory_request_id LEFT OUTER JOIN
dbo.location AS location_1 INNER JOIN
dbo.sites AS sites_1 ON location_1.site_id =
sites_1.site_id ON dbo.inventory_request.staging_location_id =
location_1.location_id


******************CONVERTED TO ACCESS STATEMENT****************
SELECT inventory_request.inventory_request_id,
inventory_request.request_number, inventory_request.creation_date,
inventory_request.last_modified_date,
inventory_request.due_date,
inventory_request.ship_date, inventory_request.request_status,
inventory_request.customer_id, inventory_request.carrier,
inventory_request.tracking_number,
inventory_request.is_canceled, inventory_request.record_status,
inventory_request.line_item_count,
inventory_request.parent_id,
inventory_request.backorder_id, inventory_request.is_printed,
location_1.code AS stage_location,
sites_1.site_name AS stage_site,
vIRCombo.item_number, vIRCombo.description, vIRCombo.request_quantity,
vIRCombo.note_text,
vIRCombo.picked_quantity,
vIRCombo.shipped_quantity, vIRCombo.line_item_id, vIRCombo.cost
FROM ((vIRCombo INNER JOIN
inventory_request ON vIRCombo.inventory_request_id
= inventory_request.inventory_request_id) LEFT OUTER JOIN
(location location_1 INNER JOIN
sites sites_1 ON location_1.site_id =
sites_1.site_id) ON inventory_request.staging_location_id =
location_1.location_id)

Thanks,

Tony K.

 

[Cor Ligthert[MVP]]

Tony,

To find errors in SQL has a simple method, make the code shorter and test it
in our query analyzer until it is working and than start to extend it again.

Be aware that joined tables cannot be updated by the standard designer
generated code from Visual Basic. You will forever get an error if you check
the boxes for that.

Cor

 

[HSalim[MVP]]

There is nothing wrong syntactically with the converted statement.
I'll bet that your SQL2005 database has a view called vIRCombo which is not
present in your access db. Simply recreate that view as an Access query
with the same name and you should be on your merry way.
HS

 

[Tony K]

I do have the vIRCombo in my Access DB which consists of a UNION between 2
views. Each of the 2 views are present also. The vIRCombo view (query)
does work fine.

If the vIRCombo was not present, would it still respond with "Join
Expression Not Supported"?

Tony K

 

[HSalim[MVP]]

OK. My Access SQL syntax is a little rusty and I recall it is finicky
let's try this: remove the outside parentheses from the FROM clause

FROM vIRCombo
INNER JOIN inventory_request
ON vIRCombo.inventory_request_id = inventory_request.inventory_request_id
LEFT OUTER JOIN (location location_1
INNER JOIN sites sites_1
ON location_1.site_id = sites_1.site_id)
ON inventory_request.staging_location_id = location_1.location_id

if that does not work, create a new view/query that joins location and site
into vLocationSite
as
select
location.code AS stage_location,
sites.site_name AS stage_site,
location.site_id,
location.site_id
from location
INNER JOIN sites
ON location.site_id = sites.site_id

now change yur from clause to
FROM vIRCombo
INNER JOIN inventory_request
ON vIRCombo.inventory_request_id = inventory_request.inventory_request_id
LEFT OUTER JOIN vlocationSite LS
ON inventory_request.staging_location_id = LS.location_id

HS

I do have the vIRCombo in my Access DB which consists of a UNION between 2
views. Each of the 2 views are present also. The vIRCombo view (query)
does work fine.

If the vIRCombo was not present, would it still respond with "Join
Expression Not Supported"?

Tony K

location_1.code AS stage_location,
sites_1.site_name AS stage_site,