Linking Table Problems with Navision and Access

gerfield

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

Savatage

Is the error coming from Nav or Access?
How are you connecting? n/odbc?

gerfield

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

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?

gerfield

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

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.

gerfield

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

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?

gerfield

yeah I can get in with no problems to the other tables its just the item table

Savatage

ok try the custom fields first because this problem of same names doesn't occur in the standard nav fields.

gerfield

does it matter is the field name doesn't match the caption?

gerfield

and what if 2 captions are the same? would that cause it?

Savatage

one way to find out
try add a "z" or something to the end of the names to assure a difference

good luck!

slmaluwa

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

happydays

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.