CODEWITHSUNDEEP
ms-accessdatabase-designvba
Justin
Justin

Reputation: 972

What is the best way to prevent Access database bloat

Intro:

I am creating a Access database system that will be rolled out with multi-user functionality. But as i am creating this database in Access 2000 (Old school I know) there are quite a lot of bugs and random mysterious problems that occur when my database gets passed 40-60MB.

My question:

Has anyone got a good solution to how I can shrink this down or to prevent the bloat?

Details:

I am using many local tables combined with SQL Tables and my front-end links to a back-end SQL Server. I have already tried compact and repair but it only ever shrinks it to about 15MB and after the user has used the database a few time the bloat expands quickly to over 50-60MB!

Let me know if more detail is needed but that is the rough outline of my problem.

Many Thanks!

Upvotes: 1

Views: 2805

Answers (2)

Tom Collins
Tom Collins

Reputation: 4069

Here's some ideas for you to follow.

You said you also have a lot of local tables. Split the local tables off into yet another Access database. So you'll have 2 back-ends (1 SQL Server & 1 Access), and the front end.

Create a batch file that opens your local tables backend database with the /compact option. So, it will look something like this: 

"C:\Prog...\Microsoft...\Officexx\ C:\ProjectX_backend.mdb /compact"

Then run this batch file on a daily basis using scheduled tasks. Your frontend should never need compacting unless you edit it in any way.

Upvotes: 2

koriander
koriander

Reputation: 3258

If you are stuck with 2000, which has a quite bad reputation, then you have to dig down into your application and find out what creates the bloat. The most common reason are bulk inserts followed by deletes. Other reasons, are the use of OLE Object fields. Other reasons are programmatic changes in in form, etc objects. You really have to go through your application and find the specific cause.

An mdb file that is only connected to a backed server and does not make changes to local objects should not grow.

As for your random issues, besides some lack of stability in the 2000 version, you should look into bad RAM in the computers, bad hard drives, and broken network controllers if your mdb file is shared on the network.

Upvotes: 1

Related Questions