Append Records From Oracle To Access DB 2

[BvCF]

Trying to determine the most efficient method to import data from Oracle 8.1.7 database and append to a local Microsoft Access (Access 2000) table on a daily basis.
Have the sql script below;

SELECT DISTINCT
AL1.ACCOUNT_ID as AcctOrig,
AL1.ACCOUNT_ID as AcctCurrent,
AL7.ENCOUNTER_NO as AcctNo,
AL2.CUSTOMER_TYPE as CustType,
AL6.LAST_NAME as LastName,
AL6.FIRST_NAME as FirstName,
AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS as BalAfterInsPymts,
AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS as BalAfterAllPymts,
AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as OrigRatio ,
AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS as OthPymts,
AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES)as CoveredCharges,
AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig,
AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT) as AllowCurrent,

FROM
CV_OCXVW.ACCOUNT_PAYOR AL1,
CV_OCXVW.CUSTOMER_ENCOUNTER AL2,
CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5,
CV_OCXVW.CUSTOMER AL6,
CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7

WHERE AL2.AcctNo = AL1.AcctNo
AND AL2.AcctNo = AL5.AcctNo
AND AL6.CUSTOMER_NO = AL2.CUSTOMER_NO
AND AL7.AcctNo = AL1.AcctNo
AND AL1.ACCOUNT_ID Not In ('D15','D16','D17')
AND AL2.EXPECTED_PAYMENT>0
AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037')
AND AL1.RANK=1
AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012','47013')
AND AL7.PAYMENT_DATE IN (
SELECT AL14.PAYMENT_DATE
FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14
WHERE AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012',

'47013'))
AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

GROUP BY
AL1.ACCOUNT_ID,
AL1.ACCOUNT_ID,
AL7.AcctNo,
AL2.CustType,
AL6.LAST_NAME,
AL6.FIRST_NAME,

HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
ORDER BY AL7.DATE_INTERFACED

Options include;

Sql Passthrough queries and use of temp table in Access
or ADO Recordsets

Linking to the Oracle tables is not an option due to the complexity of the SQL.

Currently, I have several passthrough queries and read where one can drag them to a new query and convert to an append query. I have tried this but have not been able to get it to work.

On a daily basis, importing no more than 100 records.

Based on the limited number of records, which of the above-mentioned options would be preferable? Also read about the use of functions to accomplish this.

Thanks for any insight.

 

[SantaMufasa]

BvCF,

I don't know if you have posted a similar inquiry on the Access forum, but that is probably the best venue to resolve your question, since your question centers around features and limitations on the Access end of things rather than the Oracle end. I recommend you look to the Access experts (that may, in fact, have already dealt with this issue).

Once you obtain a resolution, please do post the outline of your findings here.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddiamond]

Currently, I have several passthrough queries and read where one can drag them to a new query and convert to an append query. I have tried this but have not been able to get it to work.

I'm not sure why you want to store your data in a local access table, but wrapping your pass-thru query in an access append query should work fine. I do it all the time. In what way is it not working? If access is giving you an error message, what is it?

 

[BvCF]

ddiamond,

I was finally able to wrap the pass-thru query in an access append query.

Would the use of ADO within a module be the preferred way to extract data from Oracle to append to a local Access table?

Thoughts? Comments? Advantages? Disadvantages?

 

[ddiamond]

If you needed to call an oracle stored procedure, ADO would definately be the best way to go. For your relatively simple application, however, I don't think ADO would yield any real benifit, and it would be more code intensive. I'm a bit old-school, and microsoft would probably disagree, but I think for your application, wrapping a pass-thru query in an append query makes the most sense. And microsoft would like you to believe that ADO is faster than DAO and ODBC, but I personally have not noticed any real performance differences. If anything, ADO seems to be a tiny bit slower. But as Dave pointed out, this is really a discussion for an MS ACCESS forum. You will probably find similar discussions there.

 

[BvCF]

ddiamond,

Thanks for the insight.

So, once you wrapped the pass-thru query in an access append query, have you used Windows scheduler to run the query every day?

 

[ddiamond]

Yes. We have an over night process that moves data from our source database, DB2, to our reporting database, oracle. We are using MS Access as our middleware to facilitate this. Windows sceduler kicks off the process every morning at 3:30am. It seems to be fairly reliable.

 

[BvCF]

Has anyone else ever used a pass-thru query that is wrapped in an access append query to append data via a scheduler to a local Access table?