Database Growth

[Allen]

I use Access for billing for a water utility, i also use it for meter
reading. When i import the reading database back for billing & print bills,
my entire database grows approx. 30%. Under normal conditions the database is
80 megs and grows 2% a month, until billing time. Other than compacting
anyone have a suggestion to keep it from growing so much?

 

[pietlinden]

I use Access for billing for a water utility, i also use it for meter
reading. When i import the reading database back for billing & print bills,
my entire database grows approx. 30%. Under normal conditions the database is
80 megs and grows 2% a month, until billing time. Other than compacting
anyone have a suggestion to keep it from growing so much?

Maybe just set it to compact on close.

 

[a a r o n . k e m p f]

if you are _ACTUALLY_ trying to use Access for municipal use-- you
really should upsize to SQL Server.

Access isn't licensed for municipal usage.

-Aaron

 

[Tony Toews [MVP]]

if you are _ACTUALLY_ trying to use Access for municipal use-- you
really should upsize to SQL Server.

Access isn't licensed for municipal usage.

Now that's an utter lie.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron_kempf]

I don't see if written anywhere that it is allowed for municipal
usage.

Sorry. I've seen legal papers on this, when I worked in MS marketing
this last winter.
Access Databases are not 'up to par' when it comes to work with gov't
data.

-Aaron

 

[Tony Toews [MVP]]

I don't see if written anywhere that it is allowed for municipal
usage.

So. Find something where it's not allowed for municipal usage.

Sorry. I've seen legal papers on this, when I worked in MS marketing
this last winter.
Access Databases are not 'up to par' when it comes to work with gov't
data.

For some data Access is not secure enough. This has nothing to do
with municipalities.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

wtf are you talking about?

It's not ENTERPRISE level let alone municipal level

-Aaron

 

[Allen]

I run a privately owned public water utility. i wrote the software and i own
a legal copy. My server has 12 gigs of memory and 800 megs of hard drives. It
only grows dramaticly when printing bills and import data froma flash drive.

 

[John W. Vinson]

I run a privately owned public water utility. i wrote the software and i own
a legal copy. My server has 12 gigs of memory and 800 megs of hard drives. It
only grows dramaticly when printing bills and import data froma flash drive.

It's probably the import. How are you doing so? What is the format of the data
on the flash drive? If you Import into a new table - or *probably* even into
an existing one - then Access may well reserve more space than is actually
needed (and not free it up afterward).

Try linking to the external file and running an Append query, or if that
doesn't help, writing VBA to step through the external file and add records to
a recordset (likely much slower performance though).

 

[Allen]

All the files used are access database file. I do not actually import the
reading data back in, i copy the database file back onto my server, then i
open the access data file and read the current reading data. from that data i
update my master database. before calculating the new billing information and
printing the bills.

 

[John W. Vinson]

All the files used are access database file. I do not actually import the
reading data back in, i copy the database file back onto my server, then i
open the access data file and read the current reading data. from that data i
update my master database. before calculating the new billing information and
printing the bills.

It sounds like you're storing the same data in two different .mdb files, AND
also moving the data back and forth. Every time you delete a table or a record
from a .mdb file, the space it occupied is marked "used" and wasted; it can't
be readily recovered but it isn't freed up either.

If these two databases are on a fast, stable LAN then you should not need to
copy any data anywhere, but instead just use a typical "split" application
with a shared backend containing (one copy of!!!) the tables. The Forms and
Reports would be in the frontends, on the machines of the people who need
access to the data.

You're getting bloat because your operations are basically designed to create
bloat, unfortunately!

 

[a a r o n _ k e m p f]

you have 12 gb of memory-- and you're fucking stupid enough to be
using _ACCESS_?

WTF is wrong with you?

seriously kid-- move to SQL Server-- and if you've got 12 gb you might
as well get a standard edition.
Access won't ever use more than abotu 4mb of memory-- because Access
was designed back in 1982 when 'nobody was ever going to use that much
memory'.

Honestly-- no wonder you're having performance problems.
Your database doesn't support _MEMORY_.

-Aaron