General discussion
Thread display: Collapse - |
All Comments
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
ADO_Append Access Table with External Oracle Data
Any insight as to the possible reason/resolution would be sincerely appreciated.
Thank you in advance!
Dim Rs As New ADODB.Recordset, connString As String
Dim cn As New ADODB.Connection, sqlEncData As String
connString = "Provider=MSDAORA.1;" & _
"User ID=bc_juolpx;" & _
"Data Source=bcc;" & _
"Password=*****;" & _
"Persist Security Info=True"
cn.ConnectionString = connString
cn.Open connString
sqlEncData = "SELECT ep.account_id, pe.encounter_no, pt.Last_Name, pt.medical_mecords_no, pe.patient_type, pe.admit_date, pe.discharge_date, pe. total_charge, pe.expected_reimbursement, pe.date_billed, 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_reimbursement from encounter_payor ep, patient_encounter pe, encounter_transaction_details etd, patient pt, and ep.account Not In ('CAS45',
'CAS46','CAS48')
AND epd.TRANSACTION_CODE in ('43604','43605','43606') AND pe.expected_reimbursement>0 AND pe.expected_reimbursement - pe.total_payments>0 AND ep.total_payments/pe.expected_reimbursement<=0.9 AND etd.transaction_code in ('80459','80460','80461') 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_reimbursement, ep. total_payments, pe.expected_reimbursement - ep.total_payments,
trunc(epd.payment_date), pe.expected_reimbursement - pe.total_payments, ep.total_payments/pe.expected_reimbursement, pe.total_payments - ep.total_payments, trunc(epd.date_updated), trunc(SYSDATE), pt.last_name, pt.first_name, pt.medical_records_no, pe.patient_type HAVING ((pe.total_charges - Sum (etd.adjustment_amount)) - pe.expected_reimbursement) <> 0 ORDER BY 1, 2, 10
rs.Open sqlEncData, cn, adOpenStatic, adLockReadOnly
Do Until Rs.EOF
"INSERT INTO EncDetail ([ACCOUNT],[EncNo],[LastName],[FirstName],[MRN],[PtType],[AdmitDate],[DschDate],[TotChg],[ExpReimb],[LatestBillDate],[LastPayorPymtOn],[TotInsPymts],[TotPymts],[CoveredCharges],[CalcAllow],[DateIdentif],[OrigRatio] " & _
"VALUES(" & Rs![ep.account_id] & ",'" & Rs![pe.encounter_no] & "'," & ... Rs!fieldn & ",'" & ")"
Rs.MoveNext
Loop
rs.Close
Set rs = Nothing