I cannot split my database since it is primarily
access on the terminal server by 5-6 sales people who need it to get
reports.
You still need to split the database. I am not sure why the above prevents
you from splitting?
Each user who logs on to TS gets their own directory, and gets their own
copy of the application. Further, that appcation should likely be a mde.
For "ease" of updates, you can grab Tony's auto "front end" updater here:
http://www.granite.ab.ca/access/autofe.htm
(the above will also work with TS).
Every
night, data from quickbooks is added to it. I then run a compact and
repair
procedure and it shrinks it back down to 500 MB.
Good to cmpatct and reopair.
Since you do this compact and repiar, it is not clear why your database is
growing so much during one day of user (somthing is seriloy wrong here).
For any routintes that do processing, or do things like copy, and then
deetel reords, or create tempary tables, and throw them out, you miht
ocnider using antoher external mdb that gets created each time...and then
throw away. It is not clear if you have any other substntical routines that
delete reocrds, or create temp tables of data.
My question is, do I need
to get more memory for my server or is this limitation something that
happens
after it reaches 1 GB?
It is not cleary why running a rerot would cause the file size to grow. For
the most part, running reports will not cause this.
VERY often, a change in design of the code can ellmonate those routntes that
create/deleete reocrds. You also don't mention what version of ms-access,
but several of the service packs and updates to office *and* jet can
eliomonate some serous bloating problems.
Remember, un-necessary creating/copying of temp reocrds, and ten dleeting
them will case the file to bloat. This space is only recovered when you do a
compatct and reapir. If possbile, you should try and base the reprots on
just quiers, and not creating/copying data (and, it is not clear if you are
in fact doing this right now anyway). My point here is that extra effort
needs to be made in advoiding unecewsary creating of rocrds, and dleete
them. If you are not doing this, then your bloat probelm is due to somting
else.
For the most part, users *just* runnign a reprot should not create bloat in
the mdb file size (unless, as mentoned, there is some desing issues here
where unecessary data copying is going on).
So, you just need to work your way though a few issues:
** To increase staiblriy of your appcaiton, you need to split it.
** For addtonal staibiry, you need to use a mde for each user. It makes no
sense here for you to not split, and then complain about lack of stabliry.
Split, and use a mde.
** Make sure the serivce packs to office *and* jet are installed. This is
espcially the case for access2000 (you did not mention what version by the
way).
** if there are routiens that copy/create reocrds for reprtoing, adn the
deletes those rocrds, you need to see if the desing of this can be cchanged
(remember, to reclaim space of a record deleteed, you have to do a compact
and repair...or, simply advoid creating the rocord in the first palce).
You can read up on "bloat" here:
http://www.granite.ab.ca/access/bloatfe.htm