Append/Update Table with Oracle Data-ADO

[SpeedThink]

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!!

Upon your review, you can see that there are quite a few calculated fields in the table which is generally considered to be a maintenance problem. I plan to update all fields within the table that ends with "Current."

Any insight as to what the problem could be? Is there a more efficient method to append/update data within the Access db? How would the module differ if I wanted to update the fields on the table that end with "Current?"
Do I have too many calculated fields?


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_pt_charges + ep.noncovered_wo_charges), pe.total_charges -

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

ep.total_payments/pe.expected_payment

from entity_pay ep, Preview_encounter pe, encounter_transaction_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!CID_Orig = Rs!entity_pay.account_id
Rs!CID_Current = Rs!entity_pay.account_id
Rs!EncNo = 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_Transaction_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_Transaction_Details.Adjustment_Amount)
Rs!VarianceCurrent = Rs!Preview_encounter.Expected_Reimbursment - (Preview_encounter.Total_Charges - Sum(Encounter_Transaction_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

 

[FoFa]

Does your DSN/configuration have the proper server name that matches your tns names file in the Oracle client?

 

[SpeedThink]

Append/Update Table with Oracle Data using ADO

Thanks for the insight.

I am still trying to get this to work! Basically, the design of the Access table is "demanded" by my Manager!! I have repeatedly stated that this will be a maintenance nightmare!! I have been told to finalize the project as soon as possible!

I have recently read that having 2 recordsets is the way to go. Also, I have read that "Debug.Print..." should be used.


Therefore, I will setup another recordset titled "rsa" that uses the Access table that should be updated.

Something similar to - rsa.Open "tbl_CustDetail", asOpenStatic, adLockOptimistic