Slow the Growth of my Database

[ChuckW]

Hi,

I have a sales reporting database that several sales people use. It is
located on a server and they access it through MS Access on their PC. I
created Forms, Reports and Queries for them. The tables are generated by a
program called Access Books which takes data out of Quickbooks every night
and dumps it into Access. I have this problem where The database is about
400 MB but over the course of a month will grow to about 2 GB which is when
it stops working. I then have to create a new database and import everything
into it which brings it back down to 400 MB. Then again, this cycle
continues where the database over the course of a month will again grow to
the 2 GB level. The database is read only. The reps are only clicking on
reports and get information. They never imput things. The only increase in
size should be the incremental nightly additions from Access books. Does
anyone have any idea why my database grows so large so quickly?

Thanks,

 

[Nikos Yannacopoulos]

Chuck,

To begin with, you need to compact the database after each update.

Another important point is, in case this is a monolithic database (all
users accessing the same single copy of the database on the network
drive, containing both the data and the forms / reports / queries), you
need to split it and provide each user their own local copy of the front
end. This will minimize the risk of corruption (which is very high now),
as well as help keep the bloat low, if you are using any temp tables
while creating the reports (in which case the temp tables should be in
the local FE of each user, not in the BE). For more information on
splitting databases, look at:

http://www.granite.ab.ca/access/splitapp/index.htm

by MS-Access MVP Tony Toews.

HTH,
Nikos