Bloating of .accde file

[Robjenj]

Hi guys! I need help with this one!
I work as a volunteer for a busy wildlife hospital in Australia. I have a current Access 2010 application of a front-end Client file (.accde) linked to a back-end data file (.accdb) which records all veterinary records for all patients admitted to and discharged from the hospital, which has about 600 new patient admissions per month. The back-end file has about 30,000 records currently and is about 44Mb in size. There are up to 10 running instances of the .accde front-end file, which allows the vets and vet-nurses to enter data in a manner controlled by the vba to the .accdb data file. There are no tables in the .accde front-end file, which is bloating. A recent installation of a new version of the front-end file (after C&R) was 17,636Kb in mid March, but by the end of April it had bloated to 178,012Kb. The users are all linked to this file. I installed a new version today at 17,764Kb at 11:57, but by 12:18 it had inflated itself to 18,316Kb. My web research so far has offered no remedy for this. I had always expected the data file (.accdb) to inflate, but not the front-end Client .accde file, which carries no data.
Can anyone help with this? I really need to bring stability back to the system.

 

[MarkK]

Bloat doesn't matter. I have never heard of bloat causing instability. What instability do you mean, just that the file size is not stable?

There is a Compact On Close setting for the database. You can set it programmatically like this . . .

property get FileSizeKB as long
[COLOR="Green"]    'this property just exposes the size of the current database file[/COLOR]
    with createobject("scripting.filesystemobject")
        FileSizeKB = .getfile(currentdb.Name).Size / 1024
    end with
end property

    [COLOR="Green"]'set this is some startup routine somewhere
    'set to autocompact on close if filesize exceeds 100,000kb[/COLOR]
    SetOption "Auto Compact", FileSizeKB > 100000

. . . or you can set it to always compact on close, but disk space is so cheap these days, and 100MB is not that big really, and unless you have some other performance issue with the db, this isn't even really a problem. I would say this is normal for Access, and there is no harm done.

Cheers,

 

[Robjenj]

Thanks MarkK! Your comments greatly appreciated. Yes, by stability I was referring to steadily increasing file size. If I was to set AlwaysCompactOnClose would this cause 10 CoCs one after the other as my users shut down? Running 3 instances of the file on one machine will I believe cause that machine to run slowly. I have no experience of such things!

 

[bob fitz]

Hi guys! I need help with this one!
I work as a volunteer for a busy wildlife hospital in Australia. I have a current Access 2010 application of a front-end Client file (.accde) linked to a back-end data file (.accdb) which records all veterinary records for all patients admitted to and discharged from the hospital, which has about 600 new patient admissions per month. The back-end file has about 30,000 records currently and is about 44Mb in size. There are up to 10 running instances of the .accde front-end file, which allows the vets and vet-nurses to enter data in a manner controlled by the vba to the .accdb data file. There are no tables in the .accde front-end file, which is bloating. A recent installation of a new version of the front-end file (after C&R) was 17,636Kb in mid March, but by the end of April it had bloated to 178,012Kb. The users are all linked to this file. I installed a new version today at 17,764Kb at 11:57, but by 12:18 it had inflated itself to 18,316Kb. My web research so far has offered no remedy for this. I had always expected the data file (.accdb) to inflate, but not the front-end Client .accde file, which carries no data.
Can anyone help with this? I really need to bring stability back to the system.

Perhaps I've misunderstood but that sounds to me as though ten users are using one front end file. IMHO each user should have a copy of the front end on their machine, with each front end being linked to the single back end.

 

[MarkK]

Yeah, how you're set up there is not that clear. You say you have 10 running instances of the FE, and later you talk about the accde, which is bloating, and then, "all users are linked to this file."

Bob's point is important.

 

[bob fitz]

Yeah, how you're set up there is not that clear. You say you have 10 running instances of the FE, and later you talk about the accde, which is bloating, and then, "all users are linked to this file."

Bob's point is important.

Quite, I don't have a lot of personal experience with this kind of set up. However, I have read many posts that say once split, each user must have their own front end. I believe that not using that set up can cause many problems.

 

[Robjenj]

Thank you Bob Fitz, and again MarkK! I had been suspecting for a while that the set-up may be at fault, and yes, I can confirm that your interpretation is quite correct. The set-up has been that way for a couple of years at least, and it has taken me a long time to become aware of the bloating issue. I shall change the set-up per your advice so that each user has a front-end file, communicating with the single data file. Thank you again for your time in reviewing this problem. GREATLY appreciated!