cancel
Showing results for 
Search instead for 
Did you mean: 

MS Access - Failed to retrieve data from the database

Former Member
0 Kudos

Hello,

I've developed a quite complex report (CR XI) from a SQL Server 2005 database. The report contains a number of inner and outer joins and a subreport.

I have a duplicate copy of the database held in MS Access. If I attempt to change the datasource location to the MS Access database, I'm unable to run the report and get the following error:

Failed to retrieve data from the database.

Details: ADO Error Code: 0x80040e14

Source: Microsoft JET Database Engine

Description: Join expression not supported.

SQL State: 3000

Native Error: -540806602 (Database Vendor Code: -540806602)

By changing every outer join to an inner, the report pulls in data but obviously not what is required. It seems as though it doesn't like the outer joins.......

Would this be in some way due to limitations with the JET database engine?

Thanks in advance,

Dom

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Dom,

As you said the report contains many joins, innner and outer. Crystal reports engine creates SQL query to fire on the database.

MS access is not a relational database and hence does not support many functions in SQL.

There are many different kinds of joins, like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The first three of these different join types are implemented in Access, but FULL OUTER JOIN is not.

That may be the reason for the error.

If you need to perform a FULL OUTER JOIN in Access, Google it and you will get many trics to achieve it.

Hope it is useful.

Regards,

Aditya joshi

Former Member
0 Kudos

Hi Aditya joshi,

Thanks for the reply.

Unfortunately my query doesn't contain any FULL OUTER joins, just INNER and LEFT OUTER's.

I've enclosed a parred down version of the SQL:

SELECT numerous fields

FROM (`AAL` `AAL`

INNER JOIN

((((((`CQ` `CQ`

LEFT OUTER JOIN (((`SH` `SH`

INNER JOIN ((`PA` `PA`

INNER JOIN `AL` `AL`

ON `PA`.`ProjectUPRN`=`AL`.`ProjectUPRN`)

INNER JOIN `CA` `CA`

ON `PA`.`ArchetypeID`=`CA`.`ArchetypeCode`)

ON `SH`.`ProjectUPRN`=`AL`.`ProjectUPRN`)

INNER JOIN `SD` `SD`

ON `SH`.`SurveyID`=`SD`.`SurveyID`)

INNER JOIN `F` `F`

ON `SH`.`FormID`=`F`.`FormID`)

ON `CQ`.`QuestionID`=`SD`.`QuestionID`)

LEFT OUTER JOIN `PC` `PC`

ON (`CQ`.`QuestionID`=`PC`.`QuestionID`)

AND (`AL`.`ProjectUPRN`=`PC`.`ProjectUPRN`))

INNER JOIN `FI` `FI`

ON (`CQ`.`QuestionID`=`FI`.`QuestionID`)

AND (`F`.`FormID`=`FI`.`FormID`))

LEFT OUTER JOIN `QG` `QG`

ON `CQ`.`QuestionID`=`QG`.`QuestionID`)

LEFT OUTER JOIN `CQG` `CQG`

ON `QG`.`QuestionGroupID`=`CQG`.`QuestionGroupID`)

LEFT OUTER JOIN (`E` `E`

INNER JOIN `SDE` `SDE`

ON `E`.`ElementID`=`SDE`.`ElementID`)

ON `SD`.`SurveyDataID`=`SDE`.`SurveyDataID`)

ON `AAL`.`ProjectUPRN`=`AL`.`ProjectUPRN`)

INNER JOIN `A` `A`

ON `AAL`.`AreaID`=`A`.`AreaID`

Can you see where the problem lies?

Many thanks,

Dom

Former Member
0 Kudos

A simpler example below:

Here's the query against MS Access and fails:

SELECT `AddressList`.`AddressLine1`, `AreaAddressList`.`AreaID`, `AreaAddressList`.`ProjectUPRN`, `Area`.`AreaDescription`

FROM (`AddressList` `AddressList` LEFT OUTER JOIN `AreaAddressList` `AreaAddressList` ON `AddressList`.`ProjectUPRN`=`AreaAddressList`.`ProjectUPRN`) INNER JOIN `Area` `Area` ON `AreaAddressList`.`AreaID`=`Area`.`AreaID`

Here's the query against MS SQL Server and succeeds:

SELECT "AddressList"."AddressLine1", "AreaAddressList"."AreaID", "AreaAddressList"."ProjectUPRN", "Area"."AreaDescription"

FROM ("test"."dbo"."AddressList" "AddressList" LEFT OUTER JOIN "test"."dbo"."AreaAddressList" "AreaAddressList" ON "AddressList"."ProjectUPRN"="AreaAddressList"."ProjectUPRN") INNER JOIN "test"."dbo"."Area" "Area" ON "AreaAddressList"."AreaID"="Area"."AreaID"

any ideas?

thanks