ADO - Syntax error; 2 recordsets with 1 connection

[LevelThought]

Displayed below is my ADO script that I have constructed over the last 3 weeks to append Oracle data to an Access table.

Every time I try to run it, I am presented with a syntax error!

What is wrong with the script?

Any suggestions on what modifications that I need to make so that the script can run?

Thanks in advance for your insight.


Sub ADO_AppendtblExpenseRev()

'Declare and instantiate one connection object
'and two recordset objects
Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim mysql As String
Set cnn = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

'Open the connection
cnn.Open "Provider=MSDAORA.1;" & _
"Data source=Jof;" & _
"User ID=Ltrxw;" & _
"Password=*****;" & _
"Persist Security Info=True"

'Utilize the connection just opened as the connection for
'two different recordsets
rst1.ActiveConnection = cnn
rst1.CursorType = adOpenStatic
rst1.Open "SELECT ep.account_id, ep.account_id,pe.customer_no, pt.last_name,

pt.first_name" _

pt.medical_records_no , pe.Drg_No, pe.length_of_stay, pe.Admit_Date,

pe.discharge_date, "" _

pe.total_charge , pe.total_charge, pe.expected_payment, pe.date_billed,

max(epd.payment_date)), "" _

ep.total_payments , 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), pe.total_charges -

sum(etd.adjustment_amount), trunc(SYSDATE), "" _

ep.total_payments/pe.expected_payment,

pe.expected_payment-(pe.total_charges-sum(etd.adjustment_amount))" _

from encounter_payor ep, patient_encounter pe, encounter_transaction_details etd,

patient pt," _

where (ep.customer_no = pe.customer_no and pe.customer_no = etd.customer_no and" _

pt.customer_no = ep.customer_no) and" _
ep.account_id Not In ('ABC2','GVCT','GJUI','GVCM')" _
AND epd.TRANSACTION_CODE in ('57003','57008','47028','47006') AND" _

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

ep.total_payments/pe.expected_payment<0.91 AND etd.transaction_code in" _

('7003','7090','7080','7004') and" _

trunc(epd.date_updated) = trunc(sysdate) - 15)" _

GROUP BY ep.account_id, pe.customer_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.medical_records_no " ," _

HAVING ((pe.total_charges - sum(etd.adjustment_amount)) - pe.expected_payment) <> 0

_

ORDER BY 1, 2, 10" _

'create empty recordset referencing my table
mysql = "Select * from tblHospVarRpt1 where 1<>1"
'open recordset pointing to my table.
rst2.Open mysql, CurrentProject.Connection, 3, 3

Do Until rst1.EOF
rst2.AddNew
rst2!CID_Orig = rst1!encounter_preview.account_id
rst2!CID_Current = rst1!encounter_preview.account_id
rst2!EncNo = rst1!patient_encounter.customer_no
rst2!LastName = rst1!Patient.Last_Name
rst2!FirstName = rst1!Patient.First_Name
rst2!MRN = rst1!Patient_Medical_Records_No
rst2!PatientType = rst1!preview_encounter.patient_type
rst2!AdmitDate = rst1!preview_encounter.Admit_Date
rst2!DschDate = rst1!preview_encounter.discharge_date
rst2!TotChgOrig = rst1!preview_encounter.total_charges
rst2!TotChgCurrent = rst1!preview_encounter.total_charges
rst2!ExpPymtOrig = rst1!preview_encounter.expected_payment
rst2!ExpPymtCurrent = rst1!preview_encounter.expected_payment
rst2!DateBilled = rst1!preview_encounter.date_billed
rst2!TotInsPymtsOrig = rst1!Encounter_Payments.total_payments
rst2!TotInsPymtsCurrent = rst1!Encounter_Payments.total_payments
rst2!OthPymts = rst1!preview_encounter.total_payments -
rst1!encounter_preview.total_payments
rst2!TotPymts = rst1!preview_encounter.total_payments
rst2!Bal_AfterInsPymts = rst1!preview_encounter.expected_payment -
Rs!encounter_preview.total_payments
rst2!Bal_AfterAllPymts = rst1!preview_encounter.expected_payment -
Rs!preview_encounter.total_payments
rst2!CoveredCharges = rst1!preview_encounter.total_charges -
sum(encounter_preview.noncovered_pt_charges +
encounter_preview.noncovered_wo_charges)
rst2!CalcOrig = rst1!preview_encounter.total_charges -
sum(encounter_transfer_details.adjustment_amount)
rst2!CalcCurrent = rst1!preview_encounter.total_charges -
sum(encounter_transfer_details.adjustment_amount)
Rst2!VarianceOrig = Rst1!preview_encounter.Expected_Payment -
(preview_encounter.Total_Charges - Sum(Encounter_transfer_Details.Adjustment_Amount)
Rst2!VarianceCurrent = Rst1!preview_encounter.Expected_Reimbursment -

(preview_encounter.Total_Charges - Sum(Encounter_transfer_Details.Adjustment_Amount)
rst2!OrigRatio = rst1!preview_encounter.expected_payment /

encounter_preview.total_payments
rst2!RatioLatest = rst1!preview_encounter.expected_payment /

encounter_preview.total_payments
rst2!DateIdentified = rst1!trunc(SYSDATE)
rst2!DRG = rst1!preview_encounter.DRG
rst2!LOS = rst1!preview_encounter.length_of_stay
rst2!Date_LastPymt = rst1!max(Encounter_Payment_Detail.payment_date)
rst2.Update
Set Rs = Nothing

End Sub

 

[JerryKlmns]

LevelThought,

You have a very long SQL statement for rst1. Wouldn 't it be easier to link those Oracle tables to MS-Access (permenantly or every time you run this), build the query within MS-Access enviroment, test it for the results it gives (you can "steel" the lengthy SQL that way) and then execute a bulk append query to tblHospVarRpt1 table, based on the tested query? The bulk transaction of the append query, would run faster than qoing throu rst1, especial if you use adExecuteNoRecords.

CurrentProject.Conection.Execute "Insert Into tblHospVarRpt1 Select BuiltQuery.* From BuiltQuery;", adExecuteNoRecords

Just a thought.

Any way I have spoted a missing comma on 3rd line

rst1.Open "SELECT ep.account_id, ep.account_id,pe.customer_no, pt.last_name,

pt.first_name[red],[/red]" _

Also when a table name has spaces or - you should include it in [], like [My Spaced Table-Name]

 

[LevelThought]

I have tried to perform the query by linking Oracle tables to Access. Also, have tried pass-through.

Linking Oracle tables would require quite a few text boxes on a form to handle the numerous calculated fields within the Access table. For example, how would one handle the following parts of the SQL statement using Access SQL that will "pull" the data from the Oracle database on the initial query:

max(epd.payment_date))

pe.total_charges -sum(ep.noncovered_pt_charges + ep.noncovered_wo_charges)

ep.total_payments/pe.expected_payment<0.91

pe.total_charges -sum(etd.adjustment_amount)

pe.expected_payment - pe.total_payments>0

trunc(epd.date_updated) = trunc(sysdate) - 15)

HAVING ((pe.total_charges - sum(etd.adjustment_amount)) - pe.expected_payment) <> 0

Thanks in advance for any insight.

 

[cmmrfrds]

It would be easier to read/work with if you put the Oracle sql string in a variable.

Dim oraSQL as Variant
oraSQL = "select etc........"

At this point put in a debug.print until you get it working.
Debug.Print oraSQL
Copy the SQL from the Immediate window and paste into the QBE as SQL and run to make sure the SQL is working.

rst1.Open oraSQL, cnn, adOpenStatic, adLockOptimistic