Upsizing currency fields

[Dave Stone]

This question appeared years ago in the context of Acc2K and SQL
Server 7, but no replies were posted. HOWEVER!! It still seems to be a
problem with Acc XP and SQL Server 2000. Surely someone has a fix 6
years down the line...

I used the Upsizing Wizard to build a SQL database from an Access
back-end and link it to the Access f/e. All the data appear to have
gone across OK as viewed from Enterprise Manager, but from the
front-end all Currency fields appear empty.
The b/e definition is Money, length 8, precision 19, scale 4; in the
f/e it's Currency, Decimal places 4.

Dave

[Dave Stone]

This has got me completely baffled! I generated another copy of a
table containing a currency field, this time using DTS to create the
SQL Server table. The currency data duly appeared in the Access
front-end. However, it also was now visible in other tables where
formerly it had been hidden. What's worse, after trying some forms in
the application front-end and seeing no currency data through them, I
went back to the table datasheet view and sure enough the currency
data had become invisible again! I have logged into/out of SQL Server
and stopped/started Access several times, refreshed links, and the
data has reappeared and disappeared with no discernable pattern to
date. One interesting note is that when a currency field's data is
'hidden' in Access, zero values DO appear, i.e. only non-zero values -
in practice the vast majority - are not visible. Short of redefining
all Currency columns as Numbers, I have no idea how to proceed, so any
suggestions welcome.

Dave
D.*****@ed.ac.u k (Dave Stone) wrote in message news:<1a******* *************** ****@posting.go ogle.com>...

This question appeared years ago in the context of Acc2K and SQL
Server 7, but no replies were posted. HOWEVER!! It still seems to be a
problem with Acc XP and SQL Server 2000. Surely someone has a fix 6
years down the line...

I used the Upsizing Wizard to build a SQL database from an Access
back-end and link it to the Access f/e. All the data appear to have
gone across OK as viewed from Enterprise Manager, but from the
front-end all Currency fields appear empty.
The b/e definition is Money, length 8, precision 19, scale 4; in the
f/e it's Currency, Decimal places 4.

Dave

[Rick Brandt]

Dave Stone wrote:

This has got me completely baffled! I generated another copy of a
table containing a currency field, this time using DTS to create the
SQL Server table. The currency data duly appeared in the Access
front-end. However, it also was now visible in other tables where
formerly it had been hidden. What's worse, after trying some forms in
the application front-end and seeing no currency data through them, I
went back to the table datasheet view and sure enough the currency
data had become invisible again! I have logged into/out of SQL Server
and stopped/started Access several times, refreshed links, and the
data has reappeared and disappeared with no discernable pattern to
date. One interesting note is that when a currency field's data is
'hidden' in Access, zero values DO appear, i.e. only non-zero values -
in practice the vast majority - are not visible. Short of redefining
all Currency columns as Numbers, I have no idea how to proceed, so any
suggestions welcome.

Is your ODBC driver current? I have never seen this behavior, but I have
never used the upsize wizard either.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Dave Stone]

"Rick Brandt" <ri*********@ho tmail.com> wrote in message news:<39******* ******@individu al.net>...

Is your ODBC driver current? I have never seen this behavior, but I have
never used the upsize wizard either.

I'm using 2000.85.1117.0 dated 4/8/2004, so fairly up-to-date though
it may not be the latest. I haven't seen any reference on Technet to
this problem. Further testing actually reduced it to the following
sequence:
- View table A in datasheet view: currency column data visible
- Close table A
- Open table B in datasheet view: currency column data invisible
(except if zero)
- Close table B
- View table A in datasheet view: currency column data invisible
!!!!

I have since tried various measures to circumvent this difficulty. I
hesitate to prescribe the following as a trusted procedure - it's too
early to talk of trust! However, I have money fields in SQL Server
which were upsized from Access currency columns, and I can see the
values from the Access front-end. What appeared to work was (1)not
selecting 'use regional settings...' when setting up the ODBC
datasource (2)choosing triggers rather than DRI for upsizing
integrity. I was a bit nonplussed to find that the triggers were not
generated if the wizard was run on the Access front-end, only if on
the back-end. This meant the automatic relinking that the wizard does
was on the wrong database and had to be rectified by hand which could
be tiresome for a large database.

Hope this of use to other benighted upsizers out there!

Dave