Error linking third datasource

[oticonaus]

I have a report which currently links 6 different tables from 2 different datasources (MS SQL and Maximizer). That works fine.
The current SQL query (which runs fine) is

 SELECT CLIENT.Name_Type, U_ACCOUNT_NUMBER.U_Account_Number, CLIENT.Client_Id, CLIENT.Contact_Number
 FROM   U_ACCOUNT_NUMBER U_ACCOUNT_NUMBER LEFT OUTER JOIN CLIENT CLIENT ON U_ACCOUNT_NUMBER.Client_Id=CLIENT.Client_Id
 WHERE  CLIENT.Name_Type='C'
 SELECT "acme_Customer"."Name", "acme_Customer"."Blocked", "acme_Customer"."No_"
 FROM   "navi_syd"."dbo"."acme$Customer" "acme_Customer"
 WHERE  "acme_Customer"."Blocked"<>1

 SELECT "acme_Serial_History"."Sell-to Customer", "acme_Serial_History"."Invoiced Quantity", "acme_Serial_History"."Item No_", "acme_Serial_History"."Invoice No_", "acme_Serial_History"."Credit Memo No_", "acme_Serial_History"."Invoice Date", "acme_Serial_History"."SalesPerson Code", "acme_Serial_History"."Item Type", "acme_Serial_History"."Family", "acme_Serial_History"."Order Type", "acme_Serial_History"."Entry No_"
 FROM   "navi_syd"."dbo"."acme$Serial History" "acme_Serial_History"
 WHERE  "acme_Serial_History"."Family"<>'FORD' AND ("acme_Serial_History"."Order Type"=0 OR "acme_Serial_History"."Order Type"=1) AND ("acme_Serial_History"."Invoice Date">={ts '2006-06-01 00:00:00'} AND "acme_Serial_History"."Invoice Date"<{ts '2007-03-31 00:00:01'})
 ORDER BY "acme_Serial_History"."Sell-to Customer"

 SELECT U_COMPANY_TYPE.U_Company_Type, U_COMPANY_TYPE.Contact_Number, U_COMPANY_TYPE.Client_Id
 FROM   U_COMPANY_TYPE U_COMPANY_TYPE
 WHERE  (U_COMPANY_TYPE.U_Company_Type='Honorary OBA Member' OR U_COMPANY_TYPE.U_Company_Type='OBA Member')
 ORDER BY U_COMPANY_TYPE.U_Company_Type

 SELECT U_EVENTS.U_Events, U_EVENTS.Contact_Number, U_EVENTS.Client_Id
 FROM   U_EVENTS U_EVENTS
 WHERE  U_EVENTS.U_Events='2007 Conference'

Now I need to add in a third datasource - an Access database. The database has a customer ID which matches "acme_Serial_History"."Sell-to Customer" and "acme_Customer"."No_".

When I add the third datasource and link it (Left Outer Join) to either the Customer table or the Serial History table, however, I get an error.

"Failed to retrieve data from the datasource".
Then,
"Invalid argument provided".

Any ideas what the invalid argument might be? Or at least how I can link the third datasource successfully?

 

[synapsevampire]

I don't understand what the above means, are these all seperate Command Objects? If this is all in one Command Object, you're misunderstanding how SQL and Crystal works.

What version of Crystal, and how are you connecting to the Access datasource?

If you're using FILE type connectivity for MS Access, try switching to ODBC.

At any rate the above pasted in SQL shows 4 different selects all one after the other so I'm helplessly confused.

-k

 

[oticonaus]

I am using Crystal XI. I was trying to connect to the Access datasource via the File type (DAO), but have now changed to ODBC(RDO), and still get the same error.

The SQL query I put in the code box above is what Crystal shows me when I select Database > Show SQL Query. I'm not sure why Crystal breaks the query up into 4 groups as it does - each table is definitely linked. I can send you a screenshot of the Links tab from the Database Expert if you wish.

 

[synapsevampire]

Hmmm, that is strange.

In general you can't use Left Outer joins with disparate daatsources, so I'm not surprised that it fails.

A common workaround is to LINK (not import) all of the tables into an MS Access database, build out the query using Acess (which is faster and more flexible), and then use the Query as the source for the Crystal Report.

-k