Linked table missing currency values

[Guest]

Hello, We have an Access 2003(SP2) front end that links to a SQL 2005
database. We have an ODBC connection between the Access running server and
the SQL database. When we link the tables we get no errors and it seems to
work fine but if we open a table from Access to view the contents there is
nothing showing in any fields defined in SQL as Money. It will show £0.00 but
any other value is just not showing.

I have tried re-linking the tables to the live database but with same
results and have even come in very late when the database is not in use and
relinked again with not effect.


I have searched for an answer but so far there have been no other posts with
this problem(unless I have missed them).

I would really appreciate any assistance with this.

Regards,

Roland

 

[Stefan Hoffmann]

hi Roland,

When we link the tables we get no errors and it seems to
work fine but if we open a table from Access to view the contents there is
nothing showing in any fields defined in SQL as Money. It will show £0.00 but
any other value is just not showing.

Is the data type on the SQL Server side really MONEY?

mfG
--> stefan <--

 

[Guest]

Within SQL 2005 the table column is defined as "Cost(money, null)" All other
columns show their values correctly.

I have used a test database and relinked to the test SQL version and it
works - if I relink the test front end to the live SQL database I lose the
currency values.

 

[Stefan Hoffmann]

hi Roland,

Within SQL 2005 the table column is defined as "Cost(money, null)" All other
columns show their values correctly.

I have tried this sucessfully with an user defined data type.

I have used a test database and relinked to the test SQL version and it
works - if I relink the test front end to the live SQL database I lose the
currency values.

Compare the structure of the linked table in Access in both cases. Is
the data type CURRENCY?



mfG
--> stefan <--

 

[Guest]

Hello Stefan,

The data type in both databases is Currency, with decimal places Auto.

I get the feeling this is because the SQL database is in use(it is accessed
by another server running Windows NT and Access 97 which is linked). I will
try dialing in tonight and relinking the tables, first making sure no users
have left a session logged on and gone home.

 

[Guest]

It turned out to be a problem with the ODBC connection, I recreated it and
all is now well.

Not sure what the problem was that caused this as it all looked to be
configured correctly.

Thanks Stefan for your help.

Roland