Dear Jeff:

Unless they have changed their minds, this has a 1 GB database size limit,
half that of MSDE. Not helpful for this application. I'll be getting into
this some time soon, for sure! But not with respect to the current project.

Thanks, Jeff!

Tom Ellison


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Tom

Have you looked into the new Express SQL Server 2005 -- download for free.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

"Tom Ellison" wrote in message
...
Dear John:

MSDE has same limits, but probably better storage algorithms. I'm not
sure
there would be enough benefit to switching to MSDE. Client will not
upgrade
to full SQL Server. Expense of server OS, SQL Server, plus server
hardware
would be nearly $4K I expect. No where close to what he would spend.

I've used the same procedure in changing to a Single datatype, and the
database dropped by 80% in size. Go figure! It didn't drop even 1%
changing from precision 18 to 7. No explanation from me!

So, as you can see, I had already discussed SQL Server as an alternative.
It's one I certainly like.

My initial results are in. Indexing just one numeric column causes a 83%
increase in performance. Pretty good start. Maybe enough to finish the
project. I just hope Single datatype isn't a problem. It's not dollars
and
cents, but some kind of ratios and such. Probably this will work,
though.
I'll have to explain why 9.17 can result in records that show 9.17,
although it's stored as 9.169993. Life is a series of trade-offs, eh?

I understand about the text pointer thing. This app doesn't search for
that
text column. But surely the area where the text is stored makes the
overall
database sensitive to the length of the strings. But I guess that's the
actual length, not the maximum allocated length. So now that makes sense
also. If the text were all padded to full length with spaces, that would
be
different then.

Thanks for your participation.

Tom Ellison


"John Nurick" wrote in message
...
AFAIK Jet stores Decimals like dBase does: no nibbles, but one byte per
digit, so at least 18 bytes for a decimal of precision 18. However, if
you only need 7 digits precision you could replace your Decimals with
Longs and hard-code the position of the decimal point.

OTOH Jet does not store empty space if a Text field is not full: a
10-character string will occupy the same amount of space whether the
field size is 10 or 255. (This of course means storing a size and a
pointer for each value.)

Bottom line: you're very near the limits of what Jet's designed for,
and
some flavour of SQL client/server looks like the sensible way to go.


On Tue, 7 Feb 2006 22:32:54 -0600, "Tom Ellison"
wrote:

Allowing two digits (nibbles) per byte, a decimal of precision 18
should
require 10 bytes to store (add one nibble for a sign and divide by two,
rounding up). I chose 7, although 6 would have been sufficient for all
existing data, since I expect it's going to add one for a sign anyway,
so
6
+ 1 = 7 would be 4 bytes, and 7 + 1 = 8 would be 4 bytes anyway, so
there
would be no savings there. Just my theory.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.