Access/Oracle

[LiisaM]

Hello,
I have an Access front-end/Oracle back-end. One of the queries got an ODBC--failed error today. I re-created the query, in fact, slimmed it down. It works, but takes the user 15 mins to open, takes me 2 mins. The user is one town over, I am one floor above the servers, not sure if that matters. Any ideas on why it would all of a sudden run for so long.

Thank you!!

 

[dhookom]

Can you provide any information about the query? Is it a pass-through or does it involve Access and Oracle tables? How about the SQL view?

Duane
Hook'D on Access
MS Access MVP

 

[LiisaM]

This query is utilizing linked tables. It is not a pass-through. The problem is that this company doesn't want to spend resources on developing Oracle based views or procedures for the department. Crazy, but they may have to.

 

[dhookom]

You didn't answer my question about Access tables possibly in the query. You also didn't provide the SQL view.

I have found better luck using MS Drivers for Oracle.

You can possibly speed this up by creating a pass-through to filter your Oracle data prior to your Access query.

Duane
Hook'D on Access
MS Access MVP

 

[LiisaM]

Below is the SQL and it utilizes Oracle linked tables.

SELECT tblappl.[DR#], tblappl.STATUS, tblappl.CDR, tblappl.APPLDT, tblappl.DUEDT, tblappl.MA, tblappl.TA, tblappl.EA, tblappl.MEDCLAIM, tblappl.PSYCHCLAIM, tblappl.[APPL#], tblappl.NOCE_ AS NoCE, tblappl.TYPE_ AS Type, tblappl.MAM, tblappl.MPM, tblappl.TUAM, tblappl.TUPM, tblappl.WAM, tblappl.WPM, tblappl.THAM, tblappl.THPM, tblappl.FAM, tblappl.FPM, tblappl.INTERP_ AS Interp, tblappl.TRANSP_ AS Transp, tblappl.LASTUPDATE, tblappl.WORKING_ AS Working, tblappl.[DISCOORD#], tblappl.COMMENTS AS ApplComments, tblce.SPECIALTY, tblce.CENOTES, tblce.MDNOTES, tblce.[CE#], tblce.CESTATUS, tblce.MDSELECTDT, tblce.RESCHEDULED_ AS Rescheduled, tblchoice.CHOICE, tblapptdetail.APPTSTATUS, tblapptdetail.[APPT#], tblapptdetail.SCHEDNOTES, tblperson.[CASE#], tblperson.LNAME, tblperson.LANGUAGE, tblperson.CULTURAL, tblperson.FNAME, tblperson.DOB, tblperson.SSN, tblperson.PARENTNAME, tblperson.GENDER, tblperson.ADDRESS1, tblperson.ADDRESS2, tblperson.ZIP, tblperson.CITY, tblperson.ST, tblperson.PHONE1, tblperson.PHONE2, tblperson.PRIORITY, tblperson.CONFIDENTIALITY, tblperson.COMMENTS AS PersonComments, [ApplDt]+28 AS Day28, tblce.REQUESTED_DATE AS RequestedDt, tblperson.LONG_ AS [Long], tblperson.LAT
FROM (((TBLAPPL_R AS tblappl INNER JOIN TBLCE_R AS tblce ON tblappl.[APPL#] = tblce.[APPL#]) LEFT JOIN TBLCHOICE_R AS tblchoice ON tblappl.TYPE_ = tblchoice.CHOICECODE) INNER JOIN TBLPERSON_R AS tblperson ON tblappl.[CASE#] = tblperson.[CASE#]) LEFT JOIN TBLAPPTDETAIL_R AS tblapptdetail ON tblce.[CE#] = tblapptdetail.[CE#]
WHERE (((tblappl.[DR#])=[Forms]![frmWhoAmI]![intDRTeam]) AND ((tblappl.STATUS)=3) AND ((tblce.CESTATUS)=8) AND ((tblce.MDSELECTDT) Is Null) AND ((tblapptdetail.[APPT#]) Is Null)) OR (((tblappl.[DR#])=[Forms]![frmWhoAmI]![intDRTeam]) AND ((tblappl.STATUS)=3) AND ((tblce.CESTATUS)=8) AND ((tblce.MDSELECTDT) Is Not Null) AND ((tblapptdetail.APPTSTATUS)=2));

 

[dhookom]

I would find a good resource for Oracle SQL syntax and write code to modify the SQL property of a saved pass-through query like:

Dim strSQL as String
strSql = "SELECT tblappl.[DR#], tblappl.STATUS, tblappl.CDR, tblappl.APPLDT, tblappl.DUEDT, " & vbCrLf & _
" tblappl.MA, tblappl.TA, tblappl.EA, tblappl.MEDCLAIM, tblappl.PSYCHCLAIM, " & vbCrLf & _
" tblappl.[APPL#], tblappl.NOCE_ AS NoCE, tblappl.TYPE_ AS Type, tblappl.MAM,  " & vbCrLf & _
" tblappl.MPM, tblappl.TUAM, tblappl.TUPM, tblappl.WAM, tblappl.WPM, tblappl.THAM,  " & vbCrLf & _
" tblappl.THPM, tblappl.FAM, tblappl.FPM, tblappl.INTERP_ AS Interp, " & vbCrLf & _
" tblappl.TRANSP_ AS Transp, tblappl.LASTUPDATE, tblappl.WORKING_ AS Working, " & vbCrLf
strSQL = strSQL & " tblappl.[DISCOORD#], tblappl.COMMENTS AS ApplComments, tblce.SPECIALTY, " & vbCrLf & _
" tblce.CENOTES, tblce.MDNOTES, tblce.[CE#], tblce.CESTATUS, tblce.MDSELECTDT, " & vbCrLf & _
" tblce.RESCHEDULED_ AS Rescheduled, tblchoice.CHOICE, tblapptdetail.APPTSTATUS,  " & vbCrLf & _
" tblapptdetail.[APPT#], tblapptdetail.SCHEDNOTES, tblperson.[CASE#], tblperson.LNAME, " & vbCrLf & _
" tblperson.LANGUAGE, tblperson.CULTURAL, tblperson.FNAME, tblperson.DOB, tblperson.SSN, " & vbCrLf & _
" tblperson.PARENTNAME, tblperson.GENDER, tblperson.ADDRESS1, tblperson.ADDRESS2, " & vbCrLf & _
" tblperson.ZIP, tblperson.CITY, tblperson.ST, tblperson.PHONE1, tblperson.PHONE2, " & vbCrLf & _
" tblperson.PRIORITY, tblperson.CONFIDENTIALITY, tblperson.COMMENTS AS PersonComments, " & vbCrLf & _
" [ApplDt]+28 AS Day28, tblce.REQUESTED_DATE AS RequestedDt, tblperson.LONG_ AS [Long], " & vbCrLf & _
" tblperson.LAT " & vbCrLf
strSQL = strSQL & " FROM (((TBLAPPL_R AS tblappl INNER JOIN TBLCE_R AS tblce ON " & vbCrLf & _
" tblappl.[APPL#] = tblce.[APPL#]) LEFT JOIN TBLCHOICE_R AS tblchoice ON " & vbCrLf & _
" tblappl.TYPE_ = tblchoice.CHOICECODE) INNER JOIN TBLPERSON_R AS tblperson ON " & vbCrLf & _
" tblappl.[CASE#] = tblperson.[CASE#]) LEFT JOIN TBLAPPTDETAIL_R AS tblapptdetail ON " & vbCrLf & _
" tblce.[CE#] = tblapptdetail.[CE#] " & vbCrLf
strSQL = strSQL & " WHERE (((tblappl.[DR#])=[Forms]![frmWhoAmI]![intDRTeam]) AND ((tblappl.STATUS)=3) " & vbCrLf & _
" AND ((tblce.CESTATUS)=8) AND ((tblce.MDSELECTDT) Is Null) " & vbCrLf & _
" AND ((tblapptdetail.[APPT#]) Is Null)) " & vbCrLf & _
" OR (((tblappl.[DR#])=[Forms]![frmWhoAmI]![intDRTeam]) AND ((tblappl.STATUS)=3) " & vbCrLf & _
" AND ((tblce.CESTATUS)=8) AND ((tblce.MDSELECTDT) Is Not Null) " & vbCrLf & _
" AND ((tblapptdetail.APPTSTATUS)=2))"
CurrentDb.QueryDefs("Your P-T Query Name Here").SQL = strSQL

This code uses Access/JET sql syntax. You would need to modify this and insert values where there are references to controls on forms.

Duane
Hook'D on Access
MS Access MVP

 

[LiisaM]

I think that's a good option. Thank you for your time.