Access mdb file growing 2

[Goyo1]

I helped co-write an access database. For some reason, the data file has started to grow. it was 100mg less than a month ago, and now its 120mg. It seems to have doubled in size in the last few months for some reason.

I have repaired and compacted it, but nothing has changed. Any ideas?

 

[scriverb]

You have now been introduced to ACCESS file "bloat". You see the .mdb file continually is growing. During the processing of your data there are temp files and tables that are growing all the time. Even if you create a table, use it, then delete it the disk space for that table has been alloted to this database and now represents empty space in your file. Deleted records don't make the database smaller. There is just a blank spot in the file that ACCESS doesn't use over again. So, what is the answer to this problem.

It is the necessity to Compact the database periodically. This can be done in Design View. Select Tools Menu, Database Utilities, and then Compact Database. After running this utility the database will be compacted down without any empty or dead space. This should be done as needed. Different databases grow faster than others and you will just have to determine how often you want to compact it.

There are other methods to perform this activity also. If you are using A2k or higher you can make a setting in your
Tools/Options/. . . Compact On Close check box. Just check this box and everytime the database closes ACCESS performs a compact.

These above examples are compacting the front-end database. If you have a front-end and back-end design to your database the above will only compact your front-end databases. The back-end(tables) will have to be compacted through the first option above or an administrative process using a Short Cut setup to perform the compact.

Example: Shortcut Command-Line

"c:\program files\microsoft office\office\msaccess.exe" "G:\ . . pathto db .\db.mdb" /compact

This shortcut can be placed on the Adminstrator's PC so that periodically they can compact the server backend database.

One final note. To compact a database all users must be logged off and not accessing the server database.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[THWatson]

Bob
What a great explanation of the process! A star for you!

One question regarding compacting the back-end...you mention it will have to be done by the "first option" and I wasn't clear whether you meant the periodic manual method or the compact-on-close method.

The Administrator process works really well. It can be run from the command line. Is there another way? You mention creating a shortcut to it.

Thanks.

Tom

 

[Goyo1]

Bob, certainly appreciate the quick response. This has been bothering me for some time now, and my IT/golfing buddy recommended this forum.

I guess I forgot to mention I am using A2k, and do have a front/back end database. I am attempting the shortcut now to compact the backend, and will let you know what happens.

Thanks.

Greg

 

[scriverb]

The difference between the front-end and the back-end compacting process is that only one person is using the front-end on a PC so when you close out or trigger the compact manually you are assured that no one else has the file opened. On the back-end which is usually on a server and allows multiple users to be linked to it the problem exists that the compacting process cannot be copleted unless all users are logged off. This is why the back-end compacting process has to be controlled in most cases by an administrator manually or some code needs to be written to monitor the users logged on to see if all are off and then trigger the compacting process.

I have provided in the past shortcuts for the DB adminstrator so that they could execute the compacting process just by double-clicking the shortcut. I usually provide them with a series of them to work on each back-end database that they are responsibile for. The posting above gives you the format necessary to do this.

There was a link a while back that provided code for checking the users logged onto a system.mdw file which I believe could be used to see if anyone is currently using the system. If that number of users was zero(0) then the compacting could take place. I will look for that thread/FAQ and post here if I can find it.

In the meantime the following link was recently posted in another thread that takes you to a site where a free piece of software is available to be used to schedule ACCESS database actions. It includes the compacting process which can be setup to execute in off-hours at most sites.

HAL Software:

http://www.geocities.com/waddly/accory.html#Hal

I have recently done some testing of this software and it looks great. You can use it to run access of different versions and setup daily times for it to run. Download, unzip, and execute the install process to use it.

I will post anything more than I can find concerning the compacting process that may help you in future postings.

Good luck. Oh, I didn't forget, Thanks for the Star. It is much appreciated.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[THWatson]

Bob
I created a Desktop shortcut for the compacting process. Works great!

Thanks also for the link. I'll have a look.

Tom

 

[scriverb]

How much did it shrink down your backend database?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Goyo1]

Bob, I did the shortcut and ran it, took it from 121mg to less than 1. Wow, what a change. Thank you very much for the help on that.

But now have another problem. We have two computers networked (4 actually, but just trying the 2nd), and she cannot logon the database. Gets runtime error 3051, microscoft jet database engine cannot open the file ...name offile.mdb. It is already open exclusively by another user or you need permission to view this data.

We both rebooted, and it works on mine (I be the administrator), but mine works and her's doesn't. Any idea on that?

 

[scriverb]

Sometimes on a network the system.mdw file stays open after the someone logs off. The Adminstrator(yourself) was the last to logoff. Look at the Systems.mdw file and look for a file titled Systems.ldw file. This small file is created by ACCESS when somebody logs on to the workgroup. The System.mdw file is your logon or security file. But, this .ldw file is deleted after everybody is logged off. Try deleting this file. If your system let's you then you then can all log back on. If not then your network adminstrator will have to delete it because only they have the necessary permissions to delete the file in this state.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Goyo1]

I found the systems.mdw file, but there is no .ldw file anywhere. Neither on her's or on mine. I tried coping my front end to her's, but that didn't work either.

I'm stumpped.

 

[scriverb]

Do you each have a individual logon? I am assuming that you do. Usually a front-end / back-end system has a system.mdw file located on the server which is every user automatically joins when logging on. This system.mdw file is usually housed in the folder with your back-end database. Check there for the .ldb file. I mistyped the extension before, sorry about that. When you open up ACCESS on your desktop without designating the workgroup ACCESS connects to the c:\windows\system32\system.mdw file. But, production database systems usually use the shortcut command line to designate the security file to have the user join to. This is when the Username and password is prompted. So, like I said before check your back-end folder location and see if it is there.

You can check your shortcut command line for the location of the system.mdw file . Look for a command line parameter /wgrp . This will be followed by a path to the file.

Post back with your findings.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Goyo1]

Have seen an .ldb file before, only 1 kb in size, sometimes its there, sometimes not. It's not now.

We both logon with the same user name and password, so nothing unique there. so I'm back to being stumped.

Heck, hate to keep bothering you, don't know who else to bother, but I have a toll free # if you want to call me instead. 800-783-7086 if you want.

 

[Goyo1]

A star for you, Mr. Bob. You saved my database! Thanks for your unselfish assistance.