Linking Table Problems with Navision and Access

gerfield
gerfield Member Posts: 16
edited 2008-08-21 in NAV/Navision Classic Client
I'm trying to link my item table in nivision 3.7 native to Access 2003 but I keep getting an error.

"Cannot define field more than once"

Cannot define field more than once. (Error 3191)
You tried to create a table in which more than one field was defined with the same name. Each field name in a table must be unique.


I'm going batty over here I've checked every field. Is there an easy way to try and figure out whats stopping it?

Thanks for your help

Comments

  • Savatage
    Savatage Member Posts: 7,142
    Is the error coming from Nav or Access?
    How are you connecting? n/odbc?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • gerfield
    gerfield Member Posts: 16
    The error is in Access and I'm using c/odbc

    Savatage wrote:
    Is the error coming from Nav or Access?
    How are you connecting? n/odbc?
  • Savatage
    Savatage Member Posts: 7,142
    Try a new one.........
    Open Access->File->Open
    Change Files as type to ODBC Databases
    Click on Machine Data source
    (hopefully you set up a connection in Control Panel->Administrative Tools->Data Sources)
    Pick your data source->click ok
    Table list should appear->Select item table->click ok
    Select the new Item Choice & The table & Data should appear.

    Can you get this far?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • gerfield
    gerfield Member Posts: 16
    Yeah after I select the table and hit ok I get that box with the error
    Savatage wrote:
    Try a new one.........
    Open Access->File->Open
    Change Files as type to ODBC Databases
    Click on Machine Data source
    (hopefully you set up a connection in Control Panel->Administrative Tools->Data Sources)
    Pick your data source->click ok
    Table list should appear->Select item table->click ok
    Select the new Item Choice & The table & Data should appear.

    Can you get this far?
  • Savatage
    Savatage Member Posts: 7,142
    first thing i would do is goto the item table in designer
    View->Properties of every custom field 50000..59999
    the second property is the name see if any of these look like what an original would look like.

    What I mean is that sometimes a field is copied & perhaps the calcformula was slightly changed but the name property is not changed.
    Settings
    Unlike the ID, the Name does not have to be unique, although it is a good idea to assign unique names. You can enter up to 30 characters.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • gerfield
    gerfield Member Posts: 16
    so when I'm in the designer and I see the field name and than view properties the name shown in properties could be different and share the field name with another field?

    thanks for your help btw
  • Savatage
    Savatage Member Posts: 7,142
    i was speculating on the potential problem by your error message you posted
    ...You tried to create a table in which more than one field was defined with the same name. Each field name in a table must be unique.

    Something is upsetting Access.
    Can you access, say, the "customer" table without error?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • gerfield
    gerfield Member Posts: 16
    yeah I can get in with no problems to the other tables its just the item table
  • Savatage
    Savatage Member Posts: 7,142
    ok try the custom fields first because this problem of same names doesn't occur in the standard nav fields.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • gerfield
    gerfield Member Posts: 16
    does it matter is the field name doesn't match the caption?
  • gerfield
    gerfield Member Posts: 16
    and what if 2 captions are the same? would that cause it?
  • Savatage
    Savatage Member Posts: 7,142
    one way to find out :mrgreen:
    try add a "z" or something to the end of the names to assure a difference

    good luck!
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • slmaluwa
    slmaluwa Member Posts: 366
    hi
    I have seen this error before and it is mainly due to the Name property or caption of the field in NAV table.
    And as you said, mainly occurs for custom field where a programmer changes only the name. Other reason may be the field length.

    You sometime will also get "Field name contains invalid character" error, when you try to import tables into Access
    "A bove maiore discit arare minor"-"From the old ox, the young one learns to plow."
  • happydays
    happydays Member Posts: 7
    I have had this problem and it was that the programmers had not renamed the caption name in the table. This is hidden so show it in the designer and change it.
    Some things are just there to keep you mentally active.

Categories