Access database getting huuuuuuuge 1

[robatwork]

Hi,

I know next to nothing about access, but we have a database stored on a server that about 10 people use on a daily basis. People use it by simply clicking on a shortcut to the mdb file. It was setup using access 2000, and is about 5Mb in size.

At least it should be. This morning it was 163mb. By opening exclusive and compacting I have got it back down to its original size. This seems to need doing every day - any ideas what is causing it?

My suspicion is that it is something that has happened either since we have starting using software VPNs eg smoothwall, to access our clients, or since installing office 2007 on a couple of PCs.

would be grateful for some tips

-Rob

 

[KenReay]

Hi
Are you by chance storing images in the database?, if yes this is probably your problem. The perceived wisdom is to store the address (Path) of the image, and to store the images themselves outside of the dtaabase as individual files

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ed2020]

I have seen similar problems in Access databases since Access 2000 (I don't recall it occurring in prior versions, but it may have).

I'm not really sure what causes it but timer events that repeatedly open/close database and recordsets certainly make the problem worse. From what I've observed the system frontend will grow to a certain size and then stop - it won't keep growing until it hits the 2GB Access limit. Other than the amount of space the files take up I haven't really had any problems as a result of it.

Ed Metcalfe.

Please do not feed the trolls.....

 

[AlexCuse]

I have noticed performance hits because of this type of problem, and that is actually what first brought me to tek-tips.

If compaction helps, then why not just set the 'compact on close' option?

Hope this helps,

Alex



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[Ed2020]

If compaction helps, then why not just set the 'compact on close' option?

I would *never* use this option in a multi-user system. I *might* be persuaded to use it if it was a single-user system and the single user was me...

Ed Metcalfe.

Please do not feed the trolls.....

 

[robatwork]

Interesting stuff so far - thanks. There are indeed pictures in a little used part of this database -and I will get rid of them.

On the compact on close option - I didn't even know this was there - will it have any effect at all, as you can't compact it normally unless it's opened exclusively?

-Rob

 

[Hap007]

Hi Rob,

If multiple users are opening/sharing one Access mdb, then you can not use the compact on exit switch.

If you have not already done so, I might suggest creating a FrontEnd for the programs, forms, reports, queries and code and a Backend that just houses the data tables. Store the Backend on the server, then Link the FrontEnd to the Backend.

That being completed or already in done, I would place the FrontEnd on each user's PC and have your shortcut open the local FrontEnd. Now, the Compact on exit will work on the FrontEnd.

Note: I would guess that having multi users opening the one shared MDB at the same time is one of the reasons you are getting the Bloat.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[fneily]

Are your users adding data? Increases the size.
Are your users deleting data? That space isn't reused until a compact and repair. So new data increases the size.
Are you creating new queries or changing old ones? Then they must be compiled by Access first which increase space.
Pictures were already mentioned.
There are others.
Access is just a space hog.

 

[JoeAtWork]

I would strongly recommend following Hap007's advice about creating separate front-end/back-end databases. For Access databases, it's not just recommended, it's practically a "must do" if you value your data.

Where I slightly disagree is doing the automatic compacting on exit. Once you have created a front-end that has no tables of its own, there's really no reason to compact that front-end. Everytime you make a design change on the front-end, compile it, compact it once, but then leave it alone until you make another change.

As for your bloating issue, if records are being added or deleted, you should expect steady growth in the size of the backend until it is compacted. Although in your case the growth seems extreme. Are there any operations where large amounts of records are being imported to "temp" tables, and then later deleted? The space from deleted records will not be reclaimed until you do a compact.

 

[jsteph]

A lesser known but insidious space-taker is on-the-fly altering of a stored query's sql. I don't discourage this--I do it all the time--but it's good to be aware of the side effects.

For instance, if you do this in a loop, then every loop that runs the entire text of the previous sql of that querydef is saved and hidden in the database.

I noticed this while doing an archival process which broke down the movement of several million records into groups of 1000. Before the process was done I got the 'database has exceeded maximum size' error. It was most definitely due to the sql, not any temp tables or anything like that.

I know there are other ways to skin every cat, (using temporary querydef, etc) but I just wanted to make the point that the sql is saved and that text space--which in som querys can be up to 64KB--is not released until compacting.
--Jim

 

[KornGeek]

I would *never* use this option in a multi-user system. I *might* be persuaded to use it if it was a single-user system and the single user was me...

Can you please elaborate on why you wouldn't use this option?

I created a database where when the front-end exits, it counts how many users are connected to the back-end, and if it is the only connection, compacts the back-end. (Unfortunately, this was for a former employer so I do not have access to the code.) There are several customers using this system on a daily basis for the past few years. There have only been problems twice during this time with the compact failing. In both instances, it was easily recovered. This is because the sytems I used did the following:

1) rename back-end
2) compact renamed back-end back to the original file name
3) delete renamed back-end

If at any point the process fails, I still could restore the renamed back-end. Presto! No data loss or corruption.

 

[Ed2020]

KornGeek,

If it's a frontend on a local drive (or not shared) I can see no real harm in compact on close, but no real need to do it either. I have seen bloat in frontends with *no* data tables, but I have never noticed any negative effects other than the space taken up and a few hundred mb on a local drive is not something I'd lose sleep over.

If the database you are compacting contains data there are circumstances where Access can lose data from the MDB without any obvious warnings that this has occurred. Granted in a lot of cases this will be noticed by the users very quickly, but not necessarily always. Even if you have a backup this could prove problematic if not noticed immediately.

The method you describe is certainly a lot better than Access's built in compact on close method (unless it's been improved a lot since I last investigated it). It may be necessary to have an expert user (somebody who would be able to verify the system and data integrity once the process had completed) perform this task if the system is running at a remote site, but I still wouldn't allow all users to perform the task.

Ed Metcalfe.

Please do not feed the trolls.....

 

[robatwork]

Just to finish this thread - i bit the bullet and got someone in to move it to SQL on the backend and develop a front end for it...
thanks for all the advice