Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    31

    Unanswered: ORA-00936: missing expression error

    I am attempting to create a Access table from and Oracle View via a DSN-Less ODBC Connection.

    I am getting the ORA-00936: missing expression error and not sure if I am writing the sql statement correctly to access the ORacle data to create a make table within Access.

    here is my current code - keep in mind I am hiding the connection string info for security reasons.

    Public Function FTIRConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset

    If IsTableExist("tblFTIR_vw") Then
    CurrentDb.Execute ("drop table tblFTIR_vw")
    Else
    sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOC OL=TCP)" & _
    "(Host=Test)(Port=1521)))(CONNECT_DATA=(SID=ft csve rf)));" & _
    "User Id=test;Password=test;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User], Now() AS DateCreated" & _
    " INTO tblFTIR_vw" & _
    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
    " WHERE FTIR_APNO= " & Chr(39) & UCase(gAPNo) & Chr(39) & ""
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly'********
    End If
    DoCmd.OpenTable "tblFTIR_vw"
    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing
    oConn.Close
    Set oConn = Nothing
    End Function

    Thanks,

    Karen

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Your post is borderline unreadable.
    Print out strSQL, then use CUT & PASTE to move the results into SQL*Plus & see what happens.
    I suspect the strSQL contains an invalid SQL statement.

    Post the whole SQL*Plus session back here using CUT & PASTE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2006
    Posts
    31
    I do not have SQL Plus available to me.

    SELECT
    FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS, FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU, FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR,
    FTIR_LAST_REVISION_DATE, ' & sUser & 'AS [User], Now() AS DateCreated

    INTO tblFTIR_vw

    FROM FTCSVERF.FTIR_DNLD_VI_VW

    WHERE FTIR_APNO='1234'

  4. #4
    Join Date
    May 2006
    Posts
    31
    Is my process correct as far as Creating an Access table w/Oracle data view?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by kfschaefer
    I do not have SQL Plus available to me.
    So how do you test your SQL statements?

    Quote Originally Posted by kfschaefer
    FTIR_LAST_REVISION_DATE, ' & sUser & ' AS [User], Now() AS DateCreated
    This is not valid SQL (at least not for Oracle).

    * What's ' & sUser & ' supposed to do?
    * [User] is not a valid identifier in Oracle. Leave out the brackets
    * now() is not a valid Oracle function. You probably want CURRENT_DATE or CURRENT_TIMESTAMP

  6. #6
    Join Date
    May 2006
    Posts
    31
    Please keep in mind I am using MS Access, VBA and Not Oracle. My connection string seems to be working it is the sql portion of trying to get data from Oracle into an Access table I am having difficulty with.

    I need to determine the current user and update the User (Access field) with the users' name.

    name a field with "As" is this correct?

    k

  7. #7
    Join Date
    May 2006
    Posts
    31
    Tried your suggestions and now I am getting Error

    "From Keyword not found where expected"

    strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE, " & sUser & "AS User, CURRENT_DATE" & _
    " INTO tblFTIR_vw" & _
    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
    " WHERE FTIR_APNO='1234'" '" & UCase(gAPNo) & "'"
    Last edited by kfschaefer; 12-29-07 at 14:28.

  8. #8
    Join Date
    May 2006
    Posts
    31
    Trying another approach usin a subquery to build my Access table, however, How do I refrence the first sql string as the record source for 2nd sql string.

    ie. from -"******
    strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE" & _
    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
    " WHERE FTIR_APNO='12334" '" & UCase(gAPNo) & "'"
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
    CurrentDb.Execute "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User]" & _
    " INTO tblFTIR_vw" & _
    " FROM '************

    thanks,

    k

  9. #9
    Join Date
    May 2006
    Posts
    31
    [QUOTE=kfschaefer]Trying another approach usin a subquery to build my Access table, however, How do I refrence the first sql string as the record source for 2nd sql string.

    ie. from -"******
    strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE" & _
    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
    " WHERE FTIR_APNO='1233'" '" & UCase(gAPNo) & "'"
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
    CurrentDb.Execute "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User]" & _
    " INTO tblFTIR_vw" & _
    " FROM '************

    thanks,

    k

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Replace
    >FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User]" & _
    with
    FTIR_LAST_REVISION_DATE, sysdate, user

    eliminate the whole line starting with
    > INTO tblFTIR_vw" & _
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    May 2006
    Posts
    31
    Thanks for the response, however, I have determined that since I am creating a temp table those two fields are unnecessary, however I am getting an error. Trying to match field types. Accessor is not parameter accessor.

    Here is my latest code:

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE" & _
    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
    " WHERE FTIR_APNO='" & UCase(gAPNo) & "'"

    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
    Do Until adoRS.EOF
    CurrentProject.Connection.Execute _
    "INSERT INTO tblFTIR_vw(FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
    " FTIR_LAST_REVISION_DATE)" & _
    " VALUES(" & adoRS("FTIR_MEASNO") & "," & adoRS("FTIR_APNO") & ")'," & _
    adoRS("FTIR_TITLE") & "," & adoRS("FTIR_RM") & "," & adoRS("FTIR_SPS") & "," & _
    adoRS("FTIR_MIN") & "," & adoRS("FTIR_MAX") & "," & adoRS("FTIR_UNITS") & "," & _
    adoRS("FTIR_ACC") & "," & adoRS("FTIR_TYPE") & "," & adoRS("FTIR_LOC") & "," & _
    adoRS("FTIR_FLTR") & "," & adoRS("FTIR_HCO") & "," & adoRS("FTIR_LCO") & "," & _
    adoRS("FTIR_BUSNAME") & "," & adoRS("FTIR_SU") & "," & adoRS("FTIR_EU_SU") & "," & _
    adoRS("FTIR_EU") & "," & adoRS("FTIR_SPHDL") & "," & adoRS("FTIR_DESC") & "," & _
    adoRS("FTIR_MAINTCD") & "," & adoRS("FTIR_CMT") & "," & adoRS("FTIR_INSTLDWGNO") & "," & _
    adoRS("FTIR_LAST_REVISOR") & "," & adoRS("FTIR_LAST_REVISION_DATE")
    adoRS.MoveNext
    Loop

    DoCmd.OpenTable "tblFTIR_vw", acViewPreview, acReadOnly

    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing
    oConn.Close
    Set oConn = Nothing

    I have confirmed that the field types are the same. so now I am trying to step thru each field to see which is causing the problem.

    Am I on the right track does this error mean that the field types don't match?

    k

  12. #12
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by kfschaefer
    Please keep in mind I am using MS Access, VBA and [b]Not Oracle[b]. [...] trying to get data from Oracle
    In the same sentence you claim to be using and at the same time not using Oracle. So what's the deal?
    You are using Oracle if you are sending queries against an Oracle database. Therefore you need to use Oracle compatible syntax.

    If Access allows for its own SQL syntax, then the Oracle forum is not the right place to ask Access related questions.

    And please stop posting VBA code, this is not a VBA forum.
    To track down errors in your SQL, you need to extract the actual SQL and test that without VBA (it's the only way you can identify the real source of your problem)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •