Re: SQL Query Invalid operation

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 16:28:01 -0500

"Jonathan" <anonymous@discussions.microsoft.com> wrote in message
news:623EA37B-9415-42F6-9125-028762C05EA6@microsoft.com
> The tables are linked tables from an oracle database and there were
> no other error messages or number. Just the invalid operation error
> message.
>
> Here is the code I was using, the qry_BB_LOC_1 is where I get the
> error message
>
> CurrentDb.QueryDefs("qry_AP_RESOURCE_SCENARIO").SQL = _
> " SELECT DISTINCT A.* FROM " & userID & "_AIRPORT_RESOURCE AS A
> INNER JOIN " & userID & "_SCENARIO_RULE AS B" & _ " ON B.RULE_ID
> = A.RULE_ID WHERE B.SCENARIO_ID = '" & cmbBlScn & "';"
>
> CurrentDb.QueryDefs("qry_BAGGAGE_BELT").SQL = _
> "SELECT DISTINCT BB.AIRPORT_RESOURCE_KEY, AIRPORT_RESOURCE_NAME,
> AIRPORT_RESOURCE_CODE, GRAPHICS_SEQ_NO, SECTOR_CODE_STRING, " & _
> " CAPACITY, CLAIM, AIRPORT_CODE, DESCRIPTION, AR.IS_INACTIVE,
> AR.RULE_ID FROM " & _ userID & "_BAGGAGE_BELT AS BB,
> qry_AP_RESOURCE_SCENARIO AS AR WHERE " & _ " AR.AIRPORT_RESOURCE_KEY
> = BB.AIRPORT_RESOURCE_KEY;"
>
> CurrentDb.QueryDefs("qry_BELT_IN_AREA_VIEW").SQL = _
> " SELECT DISTINCT B.* FROM " & userID & "_BELTS_IN_AREA_RULE_VIEW
> AS B INNER JOIN " & userID & "_SCENARIO_RULE AS C" & _ " ON
> C.RULE_ID = B.RULE_ID WHERE C.SCENARIO_ID = '" & cmbBlScn & "';"
>
> CurrentDb.QueryDefs("qry_BAREA_RULE_VIEW").SQL = _
> " SELECT DISTINCT D.CONSIST AS ACONSIST, D.INCLUDES AS AINCLUDES
> FROM " & userID & "_BAREA_RULE_VIEW AS D INNER JOIN " & userID &
> "_SCENARIO_RULE AS E" & _ " ON E.RULE_ID = D.RULE_ID WHERE
> E.SCENARIO_ID = '" & cmbBlScn & "';"
>
> CurrentDb.QueryDefs("qry_BB_LOC_1").SQL = _
> " SELECT DISTINCT qry_BAREA_RULE_VIEW.ACONSIST AS TERMINAL,
> qry_BELT_IN_AREA_VIEW.INCLUDES FROM qry_BAREA_RULE_VIEW " & _
> "qry_BELT_IN_AREA_VIEW WHERE qry_BAREA_RULE_VIEW.AINCLUDES =
> qry_BELT_IN_AREA_VIEW.CONSIST;"

I gather, then, that these are existing queries and you are setting
their SQL properties on the fly. I observe that you are setting the
names of the queries' source tables as you do it, to incorporate the
value of userID, which I guess is either a variable or a control on the
current form. This strikes me as a bad data design -- storing
information in table names -- but it's quite likely you don't have any
control over the design of the Oracle tables. The first thing I'd do to
investigate this problem is set a breakpoint at the top of this code and
verify the values of userID and cmbBlScn at that point. Then I'd make
sure that the tables, with this userID embedded in their names, do in
fact exist.

Are these regular queries of the linked tables, or are they pass-through
queries?

Do you get the error message when you open a recordset or otherwise run
the query, or at the moment you assign the SQL string to its SQL
property? If set a breakpoint on that last line, can you then go and
successfully open all the other queries you've touched before that? If
so, and if you do that and then let the code execution proceed, do you
still get the error?

What version and service-level of Access and Jet are you using? Are you
using the Microsoft ODBC driver for Oracle, or are you using Oracle's
driver? I seem to recall seeing mention of situations that one handled
well and the other didn't.

You might try adding code to trap the error and print the
DBEngine.Errors collection. At the top of the code you would add

    On Error GoTo Err_Handler
    Dim lngErr As Long

and at the bottom, before the End Sub statement, you would add

'---- start of code -----
Exit_Point:
    Exit Sub

Err_Handler:
    With DBEngine.Errors
        For lngErr = 0 To (.Count - 1)
            Debug.Print .Item(lngErr).Number, .Item(lngErr).Description
        Next lngErr
    End With
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Point
'---- end of code ----

Then, when the error is raised, you can look in the Immediate Window for
the errors that were raised by the database engine. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages