Access file size

[Heeeeelp]

Hi Everyone,
I would appreciate some assistance here. I have created an access database which is being used at our office. I find that the file size grows rapidly and I'm just trying to understand how all this works and what I can be doing to keep the file size at a minimum. Currently, it's at 19,000 KB. When I recompile, it reduces it to 14,000. IT has stated that the database is too big and at danger of crashing yet my understanding is that access can handle much more than this. Can someone please confirm?

What can I be doing aside from recompiling and cleaning up code to keep the size at a minimum.

In addition, is anyone aware of a reliable software program that we could purchase for data recovery in the event there is a crash.

Any help would be greatly appreciated.

Regards,
Tess

 

[Ed2020]

Your IT department are talking utter nonsense (and that's putting it very politely. 19MB is *not* big for an Access database. Nor should it be too big for your network infrastructure, unless you are networking with 56K dial-up modems.

Access file sizes do increase over time as indexes get built, etc.... There is also a bug/feature in Access 2000 and later that causes frontend database containing *no data whatsoever* to grow in size so several hundred MB. This doesn't cause any particular problems other than the storage space they occupy. A reduction of ~5MB on compaction is nothing to worry about at all.

The most reliable way of retrieving data after a corruption is from a backup - hopefully your IT guys are doing this on a daily basis, however their advice to you makes me doubt their reliability in other areas as well!

Ed Metcalfe.

Please do not feed the trolls.....

 

[Crowley16]

access .mdb filesize limit = 2Gb
max table size = 1GB

your IT department must be thinking about something else...

--------------------
Procrastinate Now!

 

[Heeeeelp]

Thank you both for your assistance. You confirmed what I thought to be the case.

Tess

 

[missinglinq]

Or not thinking at all! ;0)>

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[lameid]

Concerning both size and recovery... you can reduce your chances of corruption and help keep your files smaller by using a split database (also called a front end and back end).

Front end is the application objects... Everything but tables with link to the tables.

Backend is just the tables.

Each is its own file.

I just compacted a production backend database from 205 MB to 196 MB so don't worry about it. I also aggree with the other posters but I might as well give your IT dept something to read.

All of the following can negatively impact the peformance of Access but that doesn't mean it won't work... In my opinion descending order of worst to least problematic.

Server Configuration (Win2k3 Servers default settings Dogged my databases; biggest help was putting backend's on there own partition)

Concurrent Users (more people using it at the same time, particularly updates)

Finally database size.

 

[Ed2020]

I'd add one more to lameid's list:

General network/server performance.

As MS Access is a file server database, rather than client server database, all data is processed locally. This means large amounts of data may be retrieved across the network for local processing in certain circumstances. Good development techniques can overcome this in a lot of scenarios; in other scenarios you can't.

I believe that Jet is a much underrated database engine. Its ease of use combined with its ability to handle some pretty hefty recordsets impresses me. I have some databases with well over two million records in certain tables, with a total file size of several hundred MB that run perfectly well over a network. However poor network or file server performance will even a small, well-designed database sluggish to use.

I would also add that I have *never* had an Access database corrupt to the extent that a simple compact and repair has not retrieved all my data (I cannot say the same for frontend databases - I have lost forms/code/reports etc, however this is less of a problem).

Ed Metcalfe.

Please do not feed the trolls.....

 

[missinglinq]

Sorry, when rereading the post today, I realized that my comment "Or not thinking at all! ;0)>" sounded like it was aimed aaat the poster, when it actually aimed at his IT department!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[fneily]

Another thing: Do you do alot of deleting? Access doesn't recover that space until you do a compact and repair. So if you're constantly developing, Access gets bloated.