Dynamic Pass Through Query Error 3075 Extra ) in Query Expression

[MChrist]

I have the following sub-routine placed in a form to create a dynamic pass
through query. The sub-routine was working fine until I added the outer join
(+) to the SQL, and then I started getting an Error 3075 Extra ) in Query
Expression message.

Using a Debug.print of the strSQL, the SQL generates a workable query, but
it won't write the query text to the pass through query. I've tried removing
the vbcrlf's to reduce the complexity of the query, along with shifting the
WHERE clause lines, but neither solution worked.

I also tried replacing the (+) with a CHR(40) & "+" & CHR(41) but that also
failed.

Is there a work-around so you can write more complex pass through queries?

Thank you in advance.

Mark

Private Sub Create_ptq_EndBklg2()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strMsg As String
Dim NewName As String

NewName = "ptq_EndBklg2"

'delete existing pass through query if it exists
On Error Resume Next
DoCmd.DeleteObject acQuery, NewName
On Error GoTo Err_Handler

strSQL = ""
strSQL = strSQL & "SELECT " & vbCrLf
strSQL = strSQL & " XXBH.REQUEST_ID " & vbCrLf
strSQL = strSQL & ", OOOHA.ORDER_NUMBER " & vbCrLf
strSQL = strSQL & ", DECODE (XXBH.PRODUCT_GROUP,'PLG03','Flow','RMD') AS
DIV " & vbCrLf
strSQL = strSQL & ", OOOHA.ATTRIBUTE2 " & vbCrLf
strSQL = strSQL & ", AHP.PARTY_NAME " & vbCrLf
strSQL = strSQL & ", SUM(XXBH.ORDER_AMOUNT) AS ORDER_AMOUNT " & vbCrLf
strSQL = strSQL & ", XXBH.PRODUCT_LINE " & vbCrLf
strSQL = strSQL & ", XXBH.ORDER_TYPE " & vbCrLf
strSQL = strSQL & ", OOOHA.INVOICE_TO_ORG_ID " & vbCrLf
strSQL = strSQL & ", XXBH.LINE_ID " & vbCrLf
strSQL = strSQL & ", OOOLA.PROMISE_DATE " & vbCrLf
strSQL = strSQL & ", XXBH.ORDER_PERIOD " & vbCrLf
strSQL = strSQL & ", OOOLA.REQUEST_DATE " & vbCrLf
strSQL = strSQL & ", OOOHA.SOLD_TO_ORG_ID " & vbCrLf
strSQL = strSQL & ", OOOHA.CUST_PO_NUMBER " & vbCrLf
strSQL = strSQL & ", AHCA.ACCOUNT_NUMBER " & vbCrLf
strSQL = strSQL & ", DECODE (AHCP.ATTRIBUTE2, NULL, AHCP2.ATTRIBUTE2, 0,
AHCP2.ATTRIBUTE2, AHCP.ATTRIBUTE2) AS PROFILESUBNBR " & vbCrLf
strSQL = strSQL & "FROM " & vbCrLf
strSQL = strSQL & " ONT.OE_ORDER_HEADERS_ALL OOOHA " & vbCrLf
strSQL = strSQL & ", XXRMT.XXONT_BOOKINGS_HISTORY XXBH " & vbCrLf
strSQL = strSQL & ", ONT.OE_ORDER_LINES_ALL OOOLA " & vbCrLf
strSQL = strSQL & ", AR.HZ_PARTIES AHP " & vbCrLf
strSQL = strSQL & ", APPS.HZ_CUST_ACCOUNTS AHCA " & vbCrLf
strSQL = strSQL & ", AR.HZ_CUSTOMER_PROFILES AHCP " & vbCrLf
strSQL = strSQL & ", AR.HZ_CUSTOMER_PROFILES AHCP2 " & vbCrLf
strSQL = strSQL & "WHERE " & vbCrLf
strSQL = strSQL & " OOOHA.HEADER_ID = XXBH.HEADER_ID " & vbCrLf
strSQL = strSQL & "AND XXBH.LINE_ID = OOOLA.LINE_ID " & vbCrLf
strSQL = strSQL & "AND OOOHA.SOLD_TO_ORG_ID = AHCA.CUST_ACCOUNT_ID " &
vbCrLf
strSQL = strSQL & "AND AHP.PARTY_ID = AHCA.PARTY_ID " & vbCrLf
strSQL = strSQL & "AND OOOHA.INVOICE_TO_ORG_ID = AHCP.SITE_USE_ID (+) " &
vbCrLf
strSQL = strSQL & "AND OOOHA.SOLD_TO_ORG_ID = AHCP2.CUST_ACCOUNT_ID (+) "
& vbCrLf
strSQL = strSQL & "AND AHP.PARTY_NAME NOT LIKE 'BENCHMARK%' " & vbCrLf
strSQL = strSQL & "AND XXBH.REQUEST_ID=" & Me.BackReqID & " " & vbCrLf
strSQL = strSQL & "GROUP BY " & vbCrLf
strSQL = strSQL & " XXBH.REQUEST_ID " & vbCrLf
strSQL = strSQL & ", OOOHA.ORDER_NUMBER " & vbCrLf
strSQL = strSQL & ", DECODE (XXBH.PRODUCT_GROUP,'PLG03','Flow','RMD') " &
vbCrLf
strSQL = strSQL & ", OOOHA.ATTRIBUTE2 " & vbCrLf
strSQL = strSQL & ", AHP.PARTY_NAME " & vbCrLf
strSQL = strSQL & ", XXBH.PRODUCT_LINE " & vbCrLf
strSQL = strSQL & ", XXBH.ORDER_TYPE " & vbCrLf
strSQL = strSQL & ", OOOHA.INVOICE_TO_ORG_ID " & vbCrLf
strSQL = strSQL & ", XXBH.LINE_ID " & vbCrLf
strSQL = strSQL & ", OOOLA.PROMISE_DATE " & vbCrLf
strSQL = strSQL & ", XXBH.ORDER_PERIOD " & vbCrLf
strSQL = strSQL & ", OOOLA.REQUEST_DATE " & vbCrLf
strSQL = strSQL & ", OOOHA.SOLD_TO_ORG_ID " & vbCrLf
strSQL = strSQL & ", OOOHA.CUST_PO_NUMBER " & vbCrLf
strSQL = strSQL & ", AHCA.ACCOUNT_NUMBER " & vbCrLf
strSQL = strSQL & ", OOOHA.ORG_ID " & vbCrLf
strSQL = strSQL & ", DECODE (AHCP.ATTRIBUTE2, NULL, AHCP2.ATTRIBUTE2, 0,
AHCP2.ATTRIBUTE2, AHCP.ATTRIBUTE2) " & vbCrLf

Debug.Print strSQL

'create the pass through query
Set db = CurrentDb
Set qd = db.CreateQueryDef(NewName)
qd.SQL = strSQL
qd.Connect = "ODBC;DSN=Oracle;"
qd.ODBCTimeout = 0
qd.Close

db.QueryDefs.Refresh

Exit_Routine:

Set qd = Nothing
Set db = Nothing

Exit Sub

Err_Handler:

strMsg = "The following error occurred trying to create the pass through
query:" & vbCrLf & vbCrLf _
& Err.Number & " " & Err.Description
MsgBox strMsg, vbCritical + vbOKOnly, "Error in CreateTempQuery()"

Resume Exit_Routine

End Sub

 

[Alex Dybenko]

Hi,
try this:
qd.Connect = "ODBC;DSN=Oracle;"
qd.SQL = strSQL

so you first set connect property and then SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi,

I also tried replacing the (+) with a CHR(40)& "+"& CHR(41) but that also
failed.

Oracle supports the [LEFT|RIGHT|INNER|OUTER] JOIN syntax at least since
version 8.


mfG
--> stefan <--

 

[MChrist]

Thanks to both Alex and Stefan for your suggestions.

Sleeping on the problem helped. This morning I woke with the idea to place
the Connect and Timeout assignments above SQL assignment which is what Alex
suggested, and it worked perfectly.

I'm guessing setting the Connect before the SQL assignment stops the SQL
parsing because it recognizes the query is a pass through. It's the little
things that always trip a person up.

Thanks for both of your responses.

Have a great day!

Mark

hi,

I also tried replacing the (+) with a CHR(40)& "+"& CHR(41) but that also
failed.

Oracle supports the [LEFT|RIGHT|INNER|OUTER] JOIN syntax at least since
version 8.


mfG
--> stefan <--
.