Setting SQL for Pass through qry results in a 3075 error

A

AnExpertNovice

3075
Syntax Error (Missing operator) in query expression 'B.LINE_NO NO'

The line that fails is: qdf.SQL = strSql


1. This code has not been modified in many months
2. If the SQL statement is generated, copied, and pasted it into a new query
the query executes properly, once given the ODBC connect string.
3. Since the SQL string works most of the code has been eliminated. The
majority of the code builds the SQL string.
4. IF 'B.LINE_NO NO' is changed to 'B.LINE_NO' (which would require
modifying subsequent queries) the same error occurs but for 'P1 Jan'.
5. The query is referencing an Oracle database hence the FROM DUAL in the
subquery.


Sub BuildKeyLossPqry()
On Error GoTo ErrorRoutine
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSql As String

strSql = "SELECT B.LINE_DESC, B.LINE_NO NO, LOCATION, YEAR, A.Type, P1
Jan, P2 Feb, P3 Mar, P4 Apr, P5 May, P6 Jun, P7 Jul, P8 Aug, P9 Sep, P10
Oct, P11 Nov, P12 Dec FROM glapps.slc_glrpt_fin_stmt A,
glapps.slc_glrpt_line_struct B WHERE A.LINE_STRUCT = B.LINE_STRUCT AND
A.LINE_NO = B.LINE_NO AND B.LINE_STRUCT = 'C' AND A.TYPE IN ('A', 'B') AND
A.YEAR IN (2004) AND A.LOCATION IN (SELECT 12345 FROM DUAL UNION SELECT
23456 FROM DUAL) ORDER BY B.LINE_NO, LOCATION, YEAR"

Set db = CurrentDb

Set qdf = db.QueryDefs("qryKeyAreaLossData")
qdf.SQL = strSql 'Modify SQL statment in Query "qdf"
<========== Error generated on this line

Set rst = qdf.OpenRecordset()
If rst.EOF Then
MsgBox Prompt:="BuildKeyLossPqry failed - job will abort",
Buttons:=vbOKOnly, Title:="qryKeyAreaLossData"
gboolStopProcessing = True
End If
ExitRoutine:
On Error Resume Next
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ErrorRoutine:
If Not gbooStopProcessing Then
gbooStopProcessing = True
MsgBox "BuildKeyLossPqry failed - job will abort"
End If
Resume ExitRoutine
Resume
End Sub
 
B

Brian

AnExpertNovice said:
3075
Syntax Error (Missing operator) in query expression 'B.LINE_NO NO'

The line that fails is: qdf.SQL = strSql


1. This code has not been modified in many months
2. If the SQL statement is generated, copied, and pasted it into a new query
the query executes properly, once given the ODBC connect string.
3. Since the SQL string works most of the code has been eliminated. The
majority of the code builds the SQL string.
4. IF 'B.LINE_NO NO' is changed to 'B.LINE_NO' (which would require
modifying subsequent queries) the same error occurs but for 'P1 Jan'.
5. The query is referencing an Oracle database hence the FROM DUAL in the
subquery.


Sub BuildKeyLossPqry()
On Error GoTo ErrorRoutine
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSql As String

strSql = "SELECT B.LINE_DESC, B.LINE_NO NO, LOCATION, YEAR, A.Type, P1
Jan, P2 Feb, P3 Mar, P4 Apr, P5 May, P6 Jun, P7 Jul, P8 Aug, P9 Sep, P10
Oct, P11 Nov, P12 Dec FROM glapps.slc_glrpt_fin_stmt A,
glapps.slc_glrpt_line_struct B WHERE A.LINE_STRUCT = B.LINE_STRUCT AND
A.LINE_NO = B.LINE_NO AND B.LINE_STRUCT = 'C' AND A.TYPE IN ('A', 'B') AND
A.YEAR IN (2004) AND A.LOCATION IN (SELECT 12345 FROM DUAL UNION SELECT
23456 FROM DUAL) ORDER BY B.LINE_NO, LOCATION, YEAR"

Set db = CurrentDb

Set qdf = db.QueryDefs("qryKeyAreaLossData")
qdf.SQL = strSql 'Modify SQL statment in Query "qdf"
<========== Error generated on this line

Set rst = qdf.OpenRecordset()
If rst.EOF Then
MsgBox Prompt:="BuildKeyLossPqry failed - job will abort",
Buttons:=vbOKOnly, Title:="qryKeyAreaLossData"
gboolStopProcessing = True
End If
ExitRoutine:
On Error Resume Next
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ErrorRoutine:
If Not gbooStopProcessing Then
gbooStopProcessing = True
MsgBox "BuildKeyLossPqry failed - job will abort"
End If
Resume ExitRoutine
Resume
End Sub
Click to expand...

If this code used to work, then it looks as though it HAS changed - or, it
has simply never been executed before. You are nowhere setting the Connect
property for qdf, therefore it is being executed as a Jet query, not a
passthrough query. The error you are getting is because the syntax
B.LINE_NO NO isn't valid in Access, it would need to be B.LINE_NO AS NO.
But, of course, there are other Oracle-specific elements in this query that
will not work in Access either.

If you WERE executing it as a passthrough query, then the line in question
couldn't possibly cause an error, because Access would not attempt to parse
the SQL. If it were a passthrough query with invalid Oracle SQL (which it
may or may not be, I wouldn't know), then you wouldn't get an error until
you tried to run the query (i.e. when you open the recordset), and then you
would, I think, get Access error 3146 (ODBC error), or something equally
non-specific.
 
A

AnExpertNovice

My mistake. It did change last month and... I failed to make a back up copy
of the database before making the changes. While it would be unlike me to
have not tested the changes before committing the changes it is also unlike
me to not have a backup. Basically all I was doing was changing the query
and routine names.



Month end is now done, except for this one report. I will look at the code
tonight, test it tomorrow, and will reply back as to what the problem was.



Thanks for responding. The connect property will be added tomorrow and
tested. Observing that it was omitted has already helped me considerably.
 
A

AnExpertNovice

A single letter had been deleted from the module.
The routine name is: "BuildKeyLossPqry()"
Yet, the querydef being modified was. "Set qdf =
db.QueryDefs("qryKeyAreaLossData")". The query name was changed to include
the leading "p" and all is well.

BTW, "qryKeyAreaLossData" references the pass through query
"pqryKeyAreaLossData" so the query being unintentionally modified existed
but is a Jet database. As you pointed out the SQL was for a different
server thus the failure. Your pointing that out made finding the solution
fast.

On my way home last night I realized that no connect property was ever set
or been needed. I assume this is because the queries are created by hand
and tested for that month. After creating the query by hand (which, of
course, means setting the ODBC connect string) the query is then modified
each month to allow for month (calendar) and location changes (listed in a
table).

Thanks for your help.
 
B

Brian

AnExpertNovice said:
A single letter had been deleted from the module.
The routine name is: "BuildKeyLossPqry()"
Yet, the querydef being modified was. "Set qdf =
db.QueryDefs("qryKeyAreaLossData")". The query name was changed to include
the leading "p" and all is well.

BTW, "qryKeyAreaLossData" references the pass through query
"pqryKeyAreaLossData" so the query being unintentionally modified existed
but is a Jet database. As you pointed out the SQL was for a different
server thus the failure. Your pointing that out made finding the solution
fast.

On my way home last night I realized that no connect property was ever set
or been needed. I assume this is because the queries are created by hand
and tested for that month. After creating the query by hand (which, of
course, means setting the ODBC connect string) the query is then modified
each month to allow for month (calendar) and location changes (listed in a
table).

Thanks for your help.
Click to expand...

Yep, that all makes sense. Glad you got it fixed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send an Query output with a Email using "sendObject" function 1
Programmatically creating a pass-through query 2
Error 3075 Missing Operator 7
qdf Object Variable Error 91 problem 6
Trouble with SQL String in VBA 3
Looping Through a Queries Results 5
SQL pass thru query from code always propting for DSN 5
Error: Too few paramenters 7

Top