I have 2 mdb files, the front end with all forms, reports, queries, and modules, and the backend with just the tables.<br><br>Users open and work in the front end which is linked to the backend tables.<br><br>As the users get in and out of the database, the front end and backend mdb files grow larger and larger. Front end started around 2MB and in 2 hours it is up to 4.7MB. The back end started around 1.7MB and it is up to 2.3MB after the same time period.<br><br>The search in this forum is broken so I cannot find out if anyone has had this problem before.<br><br>Can anyone shed linght on this?<br><br>Thanks in advance.<br>Shar
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Growing mdb file ! ! ! 2
- Thread starter shar
- Start date
desiboy
IS-IT--Management
This is because Access does not do any clean up or if it does it doesn't do it properly on close. You can set an option to compact the database on close. I use Access2000. I am not sure if this option also exists in previous versions. <p>Sandeep Anand<br><a href=mailto:sandeep@matatechnologies.com>sandeep@matatechnologies.com</a><br><a href= Technologies</a><br>sign up for our free Discussion list: "Access Tips And Tricks" at
MichaelRed
Programmer
Shar,<br><br>The Front end shouldn't grow. In the general case, only the database "Guru"/administrator should be able to change the Front end. It looks like everone in the universe is modifying everything in the universe.<br><br>MS Access (& most databases) do not delete old copies of objects, they just mark them as "Old" and you can't see them. However they (the databases) DO re-use the space occupied by the old stuff. MS Access (& ... ) can actually delete these "Old" objects doing a procedure (in MS Access refered to as "compacting"
, HOWEVER this can only be done when there are 0 or 1 users,thus Sandeep's suggestion will only work when the LAST USER logs out of the database.<br><br>You APPEAR to have a serious problem in how your database is being used!! Growth in an MS Access database occurs whenever you add or modify any object (form/query/module/table ...) BUT in most instances the growth is reasonably moderate (a few percent per day?). Growth rates of 50% per hour are - obviously - not supportable for any significant period of time.<br><br>The only thing which comes to readdily mind is the possibility that you have some "MakeTable" queries / operations which are being run frequently!<br><br>You should shut down this BEAST (for general use at least) and find out why both parts are growing!<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over
, HOWEVER this can only be done when there are 0 or 1 users,thus Sandeep's suggestion will only work when the LAST USER logs out of the database.<br><br>You APPEAR to have a serious problem in how your database is being used!! Growth in an MS Access database occurs whenever you add or modify any object (form/query/module/table ...) BUT in most instances the growth is reasonably moderate (a few percent per day?). Growth rates of 50% per hour are - obviously - not supportable for any significant period of time.<br><br>The only thing which comes to readdily mind is the possibility that you have some "MakeTable" queries / operations which are being run frequently!<br><br>You should shut down this BEAST (for general use at least) and find out why both parts are growing!<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over
xtreme1
Programmer
The front end may be growing due to appends to tables that occur frequently. In other words, if you're appending data to a table temporarily with a query of some sort, and then deleting it, the memory the deleted records once occupied is still used. As was stated before the only way to keep get the DB to it's "should be" size is to compact it, but doing this in a multiuser environment could become quite a hassle. Good luck! =] <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>
Are you using Access 2000?? I noticed in one of my applications, that the front end mdb grows with use. I haven't determined, if this is a bad thing or not. At first I was concerned, but the growth seems to slow down after the intial fast growth. Also, I noticed that the initial load of the forms was slow but speeded up after they were used the first time, the time at which the mdb grew significantily. My guess, is that access is saving some data to optimize the mdb and it may be a good thing -- it remains to be seen.
One more possibility - do your users create queries? These can eat up space as well. If you can create a number of muli-use parameter queries, this may help you convince users to stop writing new queries. It's bad enough when they create and delete them, but even worse when they leave them hangling around.
- Thread starter
- #7
I am working with Access 97.
There are no append or make table queries in the database. The users do not have access to any of the queries, directly. No 'Design' capability is available to them. The system just opens a main menu for them and they can go to a main form and apply filters or add/edit/delete records, or they can go to a report menu to print reports.
The front end has grown to 11MB so far. The back end has peaked at 2.4MB.
The only items that change in the forms and menus are some of the control buttons that are set to visible/invisible depending on user's login name.
Could this property changing by code be the culprit?
There are no append or make table queries in the database. The users do not have access to any of the queries, directly. No 'Design' capability is available to them. The system just opens a main menu for them and they can go to a main form and apply filters or add/edit/delete records, or they can go to a report menu to print reports.
The front end has grown to 11MB so far. The back end has peaked at 2.4MB.
The only items that change in the forms and menus are some of the control buttons that are set to visible/invisible depending on user's login name.
Could this property changing by code be the culprit?
Are the users running the front end off of the server or is there a copy on each individuals PC? If you are running off the server, then you could try putting the front end on each PC and you should be able to compact on exit. This may not be practical if you have many users.
- Thread starter
- #9
-
1
- #10
Access '97 databases grow period. Who knows why, who cares why.
The FIX, which is why you are here, is to Compact it.
A better way is make an MDE
An MDE also compacts it and keeps the size under control.
See my FAQ's on the subject.
The FIX, which is why you are here, is to Compact it.
A better way is make an MDE
An MDE also compacts it and keeps the size under control.
See my FAQ's on the subject.
-
1
- #11
I have fought the battle of compacting MDBs since version 2. As others have stated, it the nature of the beast to need user cleanup. However yours does seem to be growing uncommonly fast.
I have had success with creating an icon to do the compact and giving it to a specific user as part of their procedures. It can run minimized while they do other work. The icon properties need to have the full path to MSACCESS.EXE (not the runtime .EXE) then /COMPACT <from path & filename> <to path & filename>. Hope this helps.
Connie
I have had success with creating an icon to do the compact and giving it to a specific user as part of their procedures. It can run minimized while they do other work. The icon properties need to have the full path to MSACCESS.EXE (not the runtime .EXE) then /COMPACT <from path & filename> <to path & filename>. Hope this helps.
Connie
- Status
