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

    Question Unanswered: ORA-00936 missing expression

    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=ftcsve 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
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as this is an Oracle error its probably best to ask this question in the Oracle forum

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Then of course you have the addition issue of the Oracle datatype compatibility... You also need to make sure that the data on the Oracle DB is compatible with Access ... Oracle has some types of data that is not supported in Access and some datatypes that have to be coverted to a different type in Access ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    May 2006
    Posts
    31

    Thumbs up thanks for the comments

    thanks for the comments

    I am in the process of converting the Oracle fields so that Access can read them.

    K

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a few things seem strange:

    you plan a make-table exercise:
    1. even if everything else worked, the duplicate DateCreated field should kill it.
    2. are you sure Environ() works in a query like that?
    3. you are missing a space in Environ()As - strangely enough A muddles thru missing spaces with the )As combination, but with spaces currently being so inexpensive try adding one anyway.
    4. you then attempt to open a recordset with your wannabe make-table! if the SQL worked as make-table, it seems unlikely to return a recordset (or maybe this is just some wierd ADO thing that i don't understand).

    sorry - no constructive proposals: i don't use Oracle or ADO

    izy
    currently using SS 2008R2

Posting Permissions

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