Long to Decimal filed size

[DIOS]

I have an Access database that I tap into via VB/DAO.
I store a file size (in bytes) in a field of size Long. When I developed
the app i didnt think anyone would ever have files larger than 2,147,483,647
which is what a Long will hold. But with the rising popularity of DVD
media i know must deal with file sizes larger than a Long.

Anyway, ive been thinking about changing the field size to a Decimal
which according to the documentation can hold up to
"With a scale of 0 (no decimal places), the largest possible value is
+/-79,228,162,514,264,337,593,543,950,335"
So if i transfer my data to the new field do i risk losing integrity?
Will my queries take longer? Will my app suffer in performance?
What will I lose and what will I gain? Thanx for any advice.

AGP

 

[Duane Hookom]

I would backup your mdb file and then change the field size/type. See if it
works and report back to the rest of us.

 

[DIOS]

Im sure it will work. The question im asking is if im losing
any integrity by going to decimal. My values will always be
integer numbers and im not sure if thats what the decimal
type was meant for. I just need a type that will accept
integers bigger than the 32bit Long type.

AGP

 

[Allen Browne]

The Decimal field type will work for this kind of data.
It will yield slower performance, since 96-bit cannot be loaded into any CPU
register in one go. You would need to run a test to establish how
significant a hit it will be.

The Decimal type is only partially implemented in Access. There is no
Decimal type in VBA, so you are stuck with using a Variant of subtype
decimal, which again is a performance hit and requires more checking in your
code. There is no way to declare a Decimal constant.

Another alternative might be to use the Currency type, and store a value in
kilobytes. That should cope with everything up to 922,337,203,685,477.580
KB. Sounds adequate, though I can't promise that will always be enough:
"640KB should be enough for anyone."

 

[Rick Rothstein]

Since the Decimal data type is not a "true" data type (you declare a
variable as a Variant and then CDec a value into it), it is somewhat awkward
to work with. Why not just change the variables you want from Long to
Currency. That should be the easiest change to make.

Rick - MVP

 

[DIOS]

Well, I would like to stay with the ineger type. Im gonna test the decimal
type and see how that goes. I myself have a fast machine but the average
user
may have a slower machine so ill have to test on different setups.

AGP

 

[DIOS]

I was entertaining that type but it is not available as a field type in
Access.
Long, Decimal, Single, Double are available.

AGP

 

[DIOS]

I was wrong. Currency is available. Ill have to research that
type.

AGP

 

[DIOS]

Would a Currency type get me the same result but with
less overhead?

AGP

 

[Allen Browne]

In the Access interface, "Currency" appears as a data type.
It is not viewed as a subtype of "Number".

 

[DIOS]

yeah thats what I missed. Are there any drawbacks to using currency as the
type?

AGP

 

[Allen Browne]

No.

It's a scaled integer, fully supported in all versions of Access/JET/VBA.

 

[DIOS]

So it carries the same overhead as a Long? or a Single?

AGP

 

[Allen Browne]

Do some experimenting/timing trials if you want to see how efficiently the
implementation actually is.