• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Crystal Report combining Access and Oracle DB > Can I pass back data to Access

I have a crystal report that combines data that is in an oracle db and an access db. The oracle db is the main portion of the report and the access db is enclosed in a subreport. Thus it shows all records from the oracle db and those records from access where there is a match in the linking field. I need to be able to find out what records in access do not have a match. Can Crystal write back into the access db? Any other ideas?
0
kphockey2
Asked:
kphockey2
2 Solutions
 
peter57rCommented:
You cannot write into the Access database.

But if you are able to link the two sources then you could produce a report showing Access records which have no matching Oracle record.  It is possible to export these results as an Access table.

Use an outer join (left outer assuming Access is 'on the left' .You select records with a null key field from the Oracle record.

Pete
0
 
mlmccCommented:
From my understanding, it may be possible for CR9 to update the Access database.

Generally speaking though joined recordsets are not updatable.

To show the records in access with no corresponding record in Oracle, you need to reverse the join making the Access table the master.

Select AccessFields From
AccessTable LEFT OUTER JOIN OracleTable ON
AccessTable.KEY = OracleTable.Key
WHERE OracleTable.Key  IS NULL

mlmcc
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now