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?

 

[Pat Hartman]

Did you copy and paste the code? If so, it has syntax errors. Is that what you're asking about?

Some basics:
1. To update a table from a recordset which contains aggregated data, you need to use two recordsets. One to select and calculate the data and the second to update or insert the summarized data. You are only using a single recordset. You cannot update a recordset that contains aggregate functions. The database engine cannot identify the row you want to update.
2. You need to add a loop to your code. You need to .MoveNext through the first recordset and for each record in the first recordset insert/update a record in the second recordset.

 

[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

 

[Pat Hartman]

You might want to drop the definitions of the first three normal forms on your boss' desk late some night and claim innocence in the morning. Also, how often will you run this query? As soon as someone enters a payment, the calculated values are WRONG!