Schema.ini problem in Access 97

[JM]

I have written an application in Access 2000 that includes a procedure to
export results to a fact table for use in an OLAP tool.

By default Access 2000 exports numbers rounded to two decimal places, which
is insufficient for the client's needs. So I incorporated a procedure to
create a suitable schema file to generate 10 decimals:
---
[Result3.txt]
Format = CSVDelimited
ColNameHeader = True
NumberDigits = 10
---

The process works as expected in Access 2000; however, the client uses
Access 97 (don't ask - big IT department has to test everything for 6 months
before it can be given to users). In Access 97 the numbers are not merely
rounded to 2 decimals, they are truncated (rounded down) to 2 decimals. On a
table with 6-7M rows this adds up to an error of $30,000+.

My question is this. I understood that the schema file works the same in
Access 97 and Access 2000. Why doesn't the schema work under Access 97 and
how can I correct this so that the export produces 10 decimal places?

Many thanks

 

[JM]

I have written an application in Access 2000 that includes a procedure to

export results to a fact table for use in an OLAP tool.

Just to clarify, the Fact table is a text file which takes the name and
directory of the users choice.

 

[John Nurick]

Hi JM,

1) For a work-round, use the Format() function in your query to create
the appropriate number of decimals. You may have to modify schema.ini to
prevent it qualifying the resulting string with quote marks.

2) I may have the wrong end of the stick, but it rather sounds as if (a)
the data type of the field is Double and (b) it contains costs or prices
or some such. If so, might it be better to use the Currency data type?

I have written an application in Access 2000 that includes a procedure to
export results to a fact table for use in an OLAP tool.

By default Access 2000 exports numbers rounded to two decimal places, which
is insufficient for the client's needs. So I incorporated a procedure to
create a suitable schema file to generate 10 decimals:
---
[Result3.txt]
Format = CSVDelimited
ColNameHeader = True
NumberDigits = 10
---

The process works as expected in Access 2000; however, the client uses
Access 97 (don't ask - big IT department has to test everything for 6 months
before it can be given to users). In Access 97 the numbers are not merely
rounded to 2 decimals, they are truncated (rounded down) to 2 decimals. On a
table with 6-7M rows this adds up to an error of $30,000+.

My question is this. I understood that the schema file works the same in
Access 97 and Access 2000. Why doesn't the schema work under Access 97 and
how can I correct this so that the export produces 10 decimal places?

Many thanks

 

[JM]

Hi JM,

1) For a work-round, use the Format() function in your query to create
the appropriate number of decimals. You may have to modify schema.ini to
prevent it qualifying the resulting string with quote marks.

I understand the first part to mean that I output the number as a string
with the second part "reversing" that. I'll give that a try although I'm not
sure how to do teh second part.

2) I may have the wrong end of the stick, but it rather sounds as if (a)
the data type of the field is Double and (b) it contains costs or prices
or some such. If so, might it be better to use the Currency data type?

You are correct, this is a calculated cost.

I was under the impression that double and currency both were the same
number of bytes. ie identical precision. I've always tended to avoid it
because working in Europe, I'm always being asked to support multi currency
in a single application. There are alternate ways including the format
function to achieve this. What advantage did you perceive?

Thanks for replying

 

[John Nurick]

I understand the first part to mean that I output the number as a string
with the second part "reversing" that. I'll give that a try although I'm not
sure how to do teh second part.

I'm not sure myself. I suspect that instead of specifying
Format = CSVDelimited
you'd need to say
Format=Delimited(,)
and use calculated fields in the query to enclose text fields in quote
marks where needed.

I was under the impression that double and currency both were the same
number of bytes. ie identical precision. I've always tended to avoid it
because working in Europe, I'm always being asked to support multi currency
in a single application. There are alternate ways including the format
function to achieve this. What advantage did you perceive?

Double is an 8-byte floating point type while Currency is an 8-byte
integer with a fixed scaling of four decimal places. This means that
Currency can store exact values with up to 15 digits left of the decimal
followed by up to 4 decimal places. Double is less precise (about 12
significant figures) but can handle much larger and smaller numbers.