Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Exclamation Unanswered: Invalid Field Definition 'SYS_NC00171$' in definition of Index or Relationship

    Hi All,

    I am trying to link one Oracle table in ACCESS 2003. But when i tried to link it gives me "Invalid Field Definition 'SYS_NC00171$' in definition of Index or Relationship" error.

    I have used ODBC connection to connect Oracle.
    Table Contains 169 Columns and 1 Primary and 2 Foreign keys.
    I can not change any datatype of column because it is Prodcution table.

    Could some one please help me out in this. I have spend 5 hrs on this.... but unable to solve the issue.
    Last edited by jeevan.salunke; 11-10-09 at 21:41.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The dollar symbol ($) probably is the culprit.
    Have a nice day!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Non-typical alpha characters (and spaces) in the field name will cause many headaches (ie. !@#$%^&*(). Especially connecting through ODBC to an Oracle backend. Otherwise you may need to update your MDAC/Oracle driver.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2009
    Posts
    4
    Thanks pkstormy for reply.
    I dont have any alpha characters (and spaces) in the field name. And I have updated drivers also. But it didnt help me.

    Could any one help me furthere....

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Even though it's a linked table, open it in design view in Access and look at the indexes.
    It looks like you might have an index named SYS_NC00171$ - and Access doesn't like the "$" symbol.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Nov 2009
    Posts
    4
    Thanks RedNeckGeek for Help...
    But I am not able to link that table.. when I tried it to link, its kicks me off by giving that error. So I don’t have that table in ACCESS to See the Index.

    Please help..

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Then look at the index names on the Oracle side.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Nov 2009
    Posts
    4
    No there is no Index name "SYS_NC00171$" or "SYS_NC00171" on oracle side...
    What to do next?
    I was thinking that, there ACCESS might be inserting two extra columns while converting Oracle Table to ACCESS table. this is just my guess.
    Please let me know your thoughts.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Keep in mind that it's the ODBC driver that's doing the translation of your oracle table to MSAccess.

    I've linked Oracle tables into an MSAccess file without problems. It sounds to me like there is something (index as mentioned) or other naming convention that seems to be causing the problem. Try re-creating your ODBC DSN.

    I'd also test by creating a simple Oracle db with 1 table and linking that table to make sure the Oracle driver itself is not causing problems. If it links ok for a db with 1 simple table, then you'll need to re-examine the Oracle structure/naming scheme that's causing you problems.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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