ADO_Append/Update Access with Oracle Data

[SpeedThink via AccessMonster.com]

I am relatively new to the use of VBA and ADO to append Oracle data to a
Access Table. The code below was my first attempt and it doesn't work!!

After approximately 4 weeks at this, it appears that the query is too
complex...

Some of the fields on the local Access table are

Account_Orig
Account_Current
TotChgOrig
TotChgCurrent
VarianceOrig
VarianceCurrent
Date_LastPayorPymt

Note, there are quite a few calculated fields in the table which is
generally considered to be a maintenance problem!

I plan to update several calculated fields within the table (Fields that
are appended with "Current.") and the field titled "Date_LastPayorPymt."

Is there a more efficient method to append/update data within the Access db?
I have tried creating the query within the Access QBE grid as well as
using passthrough queries. It appears that the query is too complex for
Access SQL and the passthrough query doesn't work!!

My initial thought is that the calculated fields will be computed within
the query and then the results will be
input into the appropriate field within the appropriate table.

Do I have too many calculated fields? Is the syntax correct?

Sub ADO_AppendEncDetailNew()

Dim Rs As New ADODB.Recordset, connString As String
Dim cn As New ADODB.Connection, sqlEncData As String

connString = "Provider=MSDAORA.1;" & _
"User ID=wv_juilo;" & _
"Data Source=Mrr;" & _
"Password=juilo;" & _
"Persist Security Info=True"

cn.ConnectionString = connString
cn.Open connString

sqlEncData = "SELECT ep.account_id, pe.customer_no, pt.last_name,
pt.first_name " & _

pt.records_no, pe.drg_no, pe.length_of_stay, pe.patient_type,
pe.admit_date, pe.discharge_date,

pe.total_charge, pe.expected_payment, pe.date_billed, max(trunc
(epd.payment_date)),

ep.total_payments, pe.total_payments, pe.total_charges -

sum(ep.noncovered_ct_charges + ep.noncovered_ft_charges), pe.total_charges -

sum(etd.adjustment_amount), trunc(sysdate),

ep.total_payments/pe.expected_payment

from entity_pay ep, preview_encounter pe, encounter_transfer_details etd,
patient pt,

and

ep.account_id Not In ('CTOC','VNN','VCM','VRM','VCU','LP5')
AND epd.TRANSACTION_CODE in ('68806','68807','68808','68812') AND

pe.expected_payment>0 AND pe.expected_payment - pe.total_payments>0 AND

ep.total_payments/pe.expected_payment<0.75 AND etd.transaction_code in

('4569','4575','4580','4896') and trunc(epd.date_updated) =

trunc(sysdate) - 15) GROUP BY ep.account_id, pe.encounter_no, trunc
(pe.ADMIT_DATE),

trunc(pe.discharge_date), pe.date_billed, pe.total_payments,
pe.total_charges,

pe.expected_payment, ep. total_payments, pe.expected_payment -

ep.total_payments,trunc(epd.payment_date), pe.expected_payment -
pe.total_payments,

ep.total_payments/pe.expected_payment, pe.total_payments -
ep.total_payments,

trunc(epd.date_updated), trunc(SYSDATE), pt.last_name, pt.first_name,

pt.records_no, pe.patient_type HAVING ((pe.total_charges - Sum

(etd.adjustment_amount)) - pe.expected_payment) <> 0 ORDER BY 1, 2, 10

Rs.Open sqlEncData, cn, adOpenStatic, adLockReadOnly

Do Until Rs.EOF
"INSERT INTO tbl_CustDetail

Rs.AddNew
Rs!Account_Orig = Rs!entity_pay.account_id
Rs!Account_Current = Rs!entity_pay.account_id
Rs!CustNo = Rs!Preview_encounter.Customer_No
Rs!LastName = Rs!Patient.Last_Name
Rs!FirstName = Rs!Patient.First_Name
Rs!AdmitDate = Rs!Preview_encounter.admit_date
Rs!DschDate = Rs!Preview_encounter.discharge_date
Rs!TotChgOrig = Rs!Preview_encounter.Total_Charges
Rs!TotChgCurrent = Rs!Preview_encounter.Total_Charges
Rs!Bal_AfterInsPymts = Rs!Preview_encounter.expected_payment - Rs!
entity_pay.Total_Payments
Rs!Bal_AfterAllPymts = Rs!Preview_encounter.expected_payment - Rs!
Preview_encounter.Total_Payments
Rs!CoveredCharges = Rs!Preview_encounter.Total_Charges - Sum
(entity_pay.noncovered_ct_charges + entity_pay.noncovered_ft_charges)
Rs!CalcAllowOrig = Rs!Preview_encounter.Total_Charges - Sum
(Encounter_Transfer_Details.Adjustment_Amount)
Rs!CalcAllowCurrent = Rs!Preview_encounter.Total_Charges - Sum
(Encounter_Transaction_Details.Adjustment_Amount)
Rs!VarianceOrig = Rs!Preview_encounter.Expected_Reimbursment -
(Preview_encounter.Total_Charges - Sum
(Encounter_Transfer_Details.Adjustment_Amount)
Rs!VarianceCurrent = Rs!Preview_encounter.Expected_Reimbursment -
(Preview_encounter.Total_Charges -

Sum(Encounter_Transfer_Details.Adjustment_Amount)
Rs!OrigRatio = Rs!Preview_encounter.expected_payment /
entity_pay.Total_Payments
Rs!RatioLatest = Rs!Preview_encounter.expected_payment /
entity_pay.Total_Payments
Rs!DateIdentified = Rs!trunc(sysdate)
Rs!Date_LastPayorPymt = Rs!max(Encounter_Payment_Detail.payment_date)

Rs.Update
Set Rs = Nothing

End Sub


Thank you in advance.