Software

General discussion

Gravatar
0 Votes
Locked

ADO_Append Access Table with External Oracle Data

By lacwill ·
I have been trying to use the following to append Oracle data to an Access table without much success at all! The error message that is received is "Invalid Precision For Decimal Data Type."

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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

Can't tell which field(s)

by Tony Hopkinson In reply to ADO_Append Access Table w ...

might be going wrong without table descriptions, but either access is trying to convert an oracle field to a decimal type, or trying to put an number into a decimal access field.
Had this sort of problem before and it's dues to different databases representing 'standard' types in different ways.
I got rid of it by bringing the data as integers (or varchars) or not using decimals at all.

gravatar
Collapse -

Bringing data in as integers

by lacwill In reply to Can't tell which field(s)

How do I go about bringing the data in as an integer?

gravatar
Collapse -

Wrong forum

by deepsand In reply to ADO_Append Access Table w ...

This is a tech. problem, in search of a solution.

It properly belongs in "Technical Q&A," not in "Discussions."

Please re-post there.

Thank you.

Back to Software Forum

Start or search

Related Discussions

Related Forums