ODBC link in MSysObjects

[dlugirapfr]

Hi all,

I have big problem with connection between ODBC and Access 2007. Everything is linked correctly but I have problem with separator in decimal field.
In my country this separator is "," not ".". I found information about connection in MSysObjects.connect table that

...;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;...

I found somewhere that I need to change NUM value from NLS (default) to MS. Do you know how to edit this value?
For example Access 2007 see value "123.8" (so it is in my country "123,8" ) like "1238"

Thanks for any idea how to edit this field connection. I cannot find answer anywhere.

 

[spikepl]

Do not muck about with your connections. If everything is set up as it should be your tables will get the right value, decimal separator and all.

The issue is most likely in your SQL.

Show a specific example of the problem and say how the data got there.

 

[gemma-the-husky]

I think you may have to build the connection again, and start over. I would.

Many systems offer their own ODBC driver, which needs installing. Such drivers are not interchangeable, so you really need some advice from the ODBC provider about its use.

once you have a working ODBC connector, you should be able to use it to connect to a particular table or view within the ODBC source.

 

[dlugirapfr]

Dear Gemma

in Access 2007 you cannot change ODBC connection settings. If I make new connection the situation will be the same.
I found smth like this but I don't know how to change it:

http://www.experts-exchange.com/Database/MS_Access/Q_28423001.html

I see this decimal separator when I use another client to connect (for example Toad).

 

[gemma-the-husky]

As far as I am aware, ODBC links can be created and managed in windows control panel / administrative tools.

What I was saying was that different solution providers should/will provide facilities within their own system, and maybe their ODBC drive offered a solution.

If the data is coming as strings with embedded periods instead of commas, (probably because it's a proprietary solution) and their ODBC driver cannot fix that, then I imagine you could import the data as text, then massage the data to convert to a value field you can use.

 

[dlugirapfr]

Dear Gamma,
in Access manager I can only refresh connection but you can modify connection path.

 

[dlugirapfr]

Anyone have any idea how to resolve this problem? I spent a lot of time trying to resolve this annoying separator problem.

 

[gemma-the-husky]

I can only repeat that it must be an issue with the ODBC provider. Maybe they have a version that is compatible with a comma decimal separator, rather than a period separator.

You said that you cannot build a new ODBC link, but I am sure you can. Not in Access, but in windows/control panel/administrative tools. Whether that helps or not is a different point.

 

[vbaInet]

Is the decimal separator ok in your other tables and works in your functions too? Also, is your regional settings (in Control Panel) set to the correct value?

The NUM key seems to be provider specific so if that's what they advise then there's no harm changing it. Is this a linked table?