Append Records From Oracle To Access DB 1

[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 just converted them to a Microsoft Access append queries. (also, could I also convert the Union passthru query to a Microsoft update query?)

On a daily basis, importing no more than 200 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. What are Advantages/Disadvantages of ADO Recordsets vs. the use of sql passthru queries in combination with Access append query?

Thanks for any insight.

 

[Fattire]

I'm sure there's a lot more that you are actually asking for, but from what you've said this is what I'd do....

Option Compare Database

Dim con    As New ADODB.Connection
Dim rec    As New ADODB.Recordset
Dim strsql As String

con.Open "Provider=MSDAORA;" & "Data Source=NAME_OF_DB_HERE;" & "User ID=LOGIN_HERE;" & "Password=PWD_HERE"

strsql = ""
strsql = strsql & " SELECT DISTINCT al1.account_id acctorig "
strsql = strsql & "  ,al1.account_id acctcurrent "
strsql = strsql & "  ,al7.encounter_no acctno "
strsql = strsql & "  ,al2.customer_type custtype "
strsql = strsql & "  ,al6.last_name lastname "
strsql = strsql & "  ,al6.first_name firstname "
strsql = strsql & "  ,al2.expected_payment - al1.total_payments balafterinspymts "
strsql = strsql & "  ,al2.expected_payment - al2.total_payments balafterallpymts "
strsql = strsql & "  ,al1.total_payments / al2.expected_payment origratio "
strsql = strsql & "  ,al2.total_payments - al1.total_payments othpymts "
strsql = strsql & "  ,al2.total_charges - (al2.noncovered_dc_charges + al2.noncovered_ct_charges) coveredcharges "
strsql = strsql & "  ,al2.total_charges - SUM (al5.adjustment_amount) alloworig "
strsql = strsql & "  ,al2.total_charges - SUM (al5.adjustment_amount) allowcurrent"
strsql = strsql & "  FROM cv_ocxvw.account_payor al1 "
strsql = strsql & "      ,cv_ocxvw.customer_encounter al2 "
strsql = strsql & "      ,cv_ocxvw.account_transaction_details al5 "
strsql = strsql & "      ,cv_ocxvw.customer al6 "
strsql = strsql & "      ,cv_ocxvw.account_payment_detail al7"
strsql = strsql & " WHERE al2.acctno = al1.acctno "
strsql = strsql & "   AND al2.acctno = al5.acctno "
strsql = strsql & "   AND al6.customer_no = al2.customer_no "
strsql = strsql & "   AND al7.acctno = al1.acctno "
strsql = strsql & "   AND al1.account_id NOT IN ('D15', 'D16', 'D17') "
strsql = strsql & "   AND al2.expected_payment > 0 "
strsql = strsql & "   AND al5.transaction_code IN('86004', '86020', '86035', '86036', '86037')"
strsql = strsql & "   AND al1.RANK = 1 AND al7.transaction_code IN('47003','47008','47009','47010','47011','47012','47013')"
strsql = strsql & "   AND al7.payment_date IN ( SELECT al14.payment_date "
strsql = strsql & "                               FROM cv_ocxvw.account_payment_detail al14 "
strsql = strsql & "                              WHERE al14.transaction_code IN('47003','47008','47009','47010','47011','47012','47013'))"
strsql = strsql & "   AND TRUNC (al7.date_interfaced) = TRUNC (SYSDATE) - 1"
strsql = strsql & " GROUP BY al1.account_id "
strsql = strsql & "         ,al1.account_id "
strsql = strsql & "         ,al7.acctno "
strsql = strsql & "         ,al2.custtype "
strsql = strsql & "         ,al6.last_name ,al6.first_name"
strsql = strsql & " HAVING al2.expected_payment - al1.total_payments >= 5000"
strsql = strsql & " ORDER BY al7.date_interfaced"
Debug.Print strsql
rec.Open strsql, con, adOpenStatic, adLockOptimistic

Do While Not rec.EOF

in1 = "INSERT INTO [tblMain] values('" & rec!acctorig & "','" & rec!acctcurrent & "','" & rec!acctno & "','" & rec!custtype & "','" & rec!lastname & "','" & rec!firstname & "','" & rec!balafterinspymts & "','" & rec!balafterallpymts & "','" & rec!origratio & "','" & rec!othpymts & "','" & rec!coveredcharges & "','" & rec!alloworig & "','" & rec!allowcurrent & "')"

con.Execute in1
rec.MoveNext
Loop

rec.Close
con.Close

End Sub

just create a table in access called tblMain(or whatever you want it called) with the same amount of fields with similiar data types as the Oracle tables where data is coming from and you'll be set.

 

[Fattire]

BVCF,
Always use ADO with Oracle - passthrough queries are lame and cause problems when dealing with a high volumn of network traffic - ado is the fastest and most correct.

 

[BvCF]

Fattire,

Thanks for the insight.

Could the same methodology be applied to an update of certain fields in the local Access table, tblMain?

For example, if I wanted to update "total payments" every day for all pre-existing records in tblMain just for those records that had a field updated in Oracle.

Currently, using a passthrough union query and a temp table which appear to be quite a bit of "extra" steps. (Specifically, I use the passthrough union query to extract all records from two Oracle tables, Account_Payor and Customer_Encounter, that had a record modification as identified in my where clause. i.e. "where trunc (Account_payor.date_inserted) = trunc(sysdate)" to obtain only those records that had a record modification.)

 

[Fattire]

Yes. You could make a second table in Access that gets populated with only the changed data (tblUpdate) and specifically append the new data from that table to tblMain where the primary keys link, (account_id) and whatever else needs to be linked.

Once the append query is made (qappUpdateMain) call the append query to a button or to a form when it opens to run by using this:

Dim stDocName As String

stDocName = "qappUpdateMain"
DoCmd.OpenQuery stDocName, acNormal, acEdit

If you don't use a sysdate criteria in the append query you'll need to purge the tblUpdate before you update tblMain (so that the previous days data doesn't get updated) which essentially makes this a temp table by doing this:

Dim del  As String
Dim con1 As New ADODB.Connection
Set con1 = CurrentProject.Connection

del = "delete * from [tblUpdate]"
con.Execute del

Hope I'm on the same page as you...there are other ways you can do this of course, but this is simple enough and works.

 

[BvCF]

Thanks for the information.

Will test this and post back.

I already have a temp table setup to load only those Oracle records that are modified. Therefore, I will modify the script in your first posting to accomodate the update via load into the temp table. Also, will perform inner join on temp table and main local Access table and test.

I had planned on setting up the append and update query in the same sub within a module and using a scheduler to run every morning at 9:00 a.m.

Ideally, it would be a good idea to capture the number of records appended and updated daily.

Additionl Thoughts? Comments?

 

[Fattire]

That sounds great! It'll be very nice when its all done.

 

[BvCF]

Code stops at this point!!

rst1.Open oraSQL, cnn, adOpenStatic, adLockOptimistic '

I reviewed the length of the table names and based on research, the table name should not be more than 30 characters. Table name was 33 characters long.

Therefore, I tried to use a different schema. From cv_ocxvw to ocxvw.

Still not successful!

Any suggestions?