left join via ado

[mattjenn]

Using Ado to Access database. This query works in Access but 'fails to
initiate object' via ado. It worked fine until I added the second LEFT
JOIN.

Is there a work around?


SELECT personal_details.pid as ppid,personal_details.* ,applications.*,
jobs.position FROM (personal_details LEFT JOIN applications ON
personal_details.pid = applications.pid) LEFT JOIN jobs ON
applications.jobid = jobs.jobid

Rgds
Matt

 

[peregenem]

Using Ado to Access database. This query works in Access but 'fails to
initiate object' via ado. It worked fine until I added the second LEFT
JOIN.

Is there a work around?


SELECT personal_details.pid as ppid,personal_details.* ,applications.*,
jobs.position FROM (personal_details LEFT JOIN applications ON
personal_details.pid = applications.pid) LEFT JOIN jobs ON
applications.jobid = jobs.jobid

The ADO wildcard character is '%'. Try this in place of '*'.

 

[COBURNDAVIS]

The only work around I can suggest is to build the table 'twice' ie: build an
Interim Table using the first left join and then add the next left join to
the Interim Table.

I know this is wasteful in terms of space but Access does not seem happy
with more than one join.

By the way your query would run perfectly well if you were using
MS_SQLServer to build the table.

Cheers

Terry

 

[Michel Walsh]

Hi,


The following query, in Northwind, works fine, in DAO and in ADO.


================
SELECT Employees.EmployeeID,
Employees.LastName,
Orders.CustomerID,
[Order Details].ProductID,
[Order Details].UnitPrice

FROM (Employees LEFT JOIN Orders
ON Employees.EmployeeID=Orders.EmployeeID)

LEFT JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID;
================


and the two left joins are in the same "position" and imply the tables in
the same manner than for your query.

That seems to indicate that the "problem" sounds to be from something else
that from the database engine in itself. "fails to initiate object" is an
error by the system? ... it occurs where? If in Access-VBA, what is the
code? you use a saves query or the SQL text? .





Vanderghast, Access MVP