Front End Database Growing. 9

[Ed2020]

Several of the systems I support have a strange feature that I cannot fathom:

The systems are split into multiple MDB files - a front end application MDB and a backend (or multiple backend) data MDB.

The front ends contain *no* data at all - they only contain forms, reports, modules and queries. However during use the front end MDB files are steadily growing to ridiculous sizes. A front end of only ~2MB can grow to several hundred MB for no apparant reason.

Compacting the front end recifies the problem temporarily, but the problem persists as users start using the system again.

This problem is causing space problems on our network drives.

I have never experienced this in either Access 97 (a far less buggy product in my opinion) or Access 2002/XP.

Can anyone shed any light on what is causing this?

Purple stars available to anyone who helps!

Ed Metcalfe.

Please do not feed the trolls.....

 

[MichaelRed]

If the FE constructs temporary "table type" recordsets, they will cause 'bloat'. doesn't make any difference if they are destroyed on / before exit, as creation reserves the allocation. Destruction does not remopve it.

Another potential culprit is updating elements / objects of the FE from ANY source / reason. Even if the old object is destroyed and a new object installed with the same name, the allocation of the old object remains.

Of course these items occur in all versions - at least as far as I know.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GDGarth]

Is your front end on each user's local hard drive? If so, an easy solution is to have the front end copied from the network each time you open it. That way, it doesn't have a chance to grow.

Since most front ends are quite small, the time and overhead associated with that are minimal. If you don't want to do it each time you log on, you could copy the database on startup, once a day, once a week, whatever seems to make sense.

I've got a "Database Menu" database that does this when you click on the database name, but you can copy the file easily with a simple batch file that also opens the database. The other advantage of this is your users always have the latest version of your front end.

I'm guessing MichaelRed is correct on the reason behind the growth.

 

[PWise]

to MichaelRed
will compacting reduce this bloat
i recently started changeing my fe code from
currentdb.execute
currentdb.openrecordset
to
dim mydb as database
set mydb=curentdb
mydb.execute
mydb.openrecordset
ect.
while i noticed an improvement in speed
i also noticed that my front end size growing in size

 

[MichaelRed]

I would expect the later format does actually open a "localized" recordset - thus "bloat". I really do not currently have the resourcs (one of which would be any incentive) to delve into the exact differences. but I do know that -with the exception of compact- Ms. A. hoards all resources ever allocated - even though they become unavailable and unviewable to the user.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hymn]

You could try putting this code in your db when it closes
docmd.runcommand acCmdCompactDatabase

ps where's michael star

Hope this helps
Hymn

 

[Stevehewitt]

Hey,

I am having exactly the same problem. Thanks to Hymn and Michael - a star for both of you.

Hymn - I am a newbie in scripting. Can you help me out? - Where (a new module! Duh! ;-)) do I put this code, and how do I get it to run when a user exits?

Cheers,

Steve Hewitt

 

[MichaelRed]

with respect to the 'stars' thinggyyyyy, I DO appreciate the thanks. On the other hand, i believe that it is the perview of the thanker to offer such, and I certainly don;'t come here anticipating that there will be much offered.

I looked at my profile recently, to check if I had posted 5K times yet (in hte fall of last year, I had past 4.6K and it occured to me that I might have gone over the next "K". I was 'struck" by the glaringly obvious ratio of ~500 stars : ~5000 post. I have NO idea if this is typical of others, but it was a reminder to expect to NOT get a star.


But I do thank those who have 'donated' them.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Ed2020]

MichaelRed and all others who have replied - a star to all of you.

I'm *sure* I've never seen this in any other version of Access though......

Ed Metcalfe.

Please do not feed the trolls.....

 

[MichaelRed]

Well, you COOULD set up a small test routine and document the results, for a more in-depth understanding in the future. I would think that MANY programmers would benefit from the KNOWLEDGE (as opposed to the speculation presented so far).



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DonQuichote]

There are a few things that are stored in the backend that can make it grow. First, clipboard items are a real pain. I used to type [shift-delete] to bypass the dumb "are you sure?" question, like in explorer. In access, however, this is the keyboard shortcut for "cut". And a "cut" module is still around (and its methods are still visible in autocomplete!), it is just not visible. If you list the modules collection from code, you'll see that the invisible modules start with a "~".

Same with SQL statements in controls (listboxes, comboboxes, etc.) Once they are used, they are compiled to querydef objects that are also not visible in the database window. Again, list the queries collection and see them, starting with a "~".

If you want to make a database really small, use the decompile switch for ms-access (search the net for pages on this) and compact afterwards.

Best regards,
Willem Bogaerts

 

[hymn]

A2002
If you look in
Tools
Options
General
There is a box to click for Compact on Close

for A97
help shows this

This example uses the CompactDatabase method to change the collating order of a database. You cannot use this code in a module belonging to Northwind.mdb.

Sub CompactDatabaseX()

Dim dbsNorthwind As Database

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot

' Show the properties of the original database.
With dbsNorthwind
Debug.Print .Name & ", version " & .Version
Debug.Print " CollatingOrder = " & .CollatingOrder
.Close
End With

' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(&quot;NwindKorean.mdb&quot <> &quot;&quot; Then _
Kill &quot;NwindKorean.mdb&quot;

' This statement creates a compact version of the
' Northwind database that uses a Korean language
' collating order.
DBEngine.CompactDatabase &quot;Northwind.mdb&quot;, _
&quot;NwindKorean.mdb&quot;, dbLangKorean

Set dbsNorthwind = OpenDatabase(&quot;NwindKorean.mdb&quot

' Show the properties of the compacted database.
With dbsNorthwind
Debug.Print .Name & &quot;, version &quot; & .Version
Debug.Print &quot; CollatingOrder = &quot; & .CollatingOrder
.Close
End With

End Sub

This example uses the CompactDatabase method to change the version of a database. To run this code, you must have a Microsoft Jet version 1.1 database called Nwind11.mdb and you cannot use this code in a module belonging to Nwind11.mdb.

Sub CompactDatabaseX2()

Dim dbsNorthwind As Database
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase(&quot;Nwind11.mdb&quot

' Show the properties of the original database.
With dbsNorthwind
Debug.Print .Name & &quot;, version &quot; & .Version
Debug.Print &quot; CollatingOrder = &quot; & .CollatingOrder
.Close
End With

' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(&quot;Nwind20.mdb&quot <> &quot;&quot; Then _
Kill &quot;Nwind20.mdb&quot;

' This statement creates a compact and encrypted
' Microsoft Jet 2.0 version of a Microsoft Jet version
' 1.1 database.
DBEngine.CompactDatabase &quot;Nwind11.mdb&quot;, _
&quot;Nwind20.mdb&quot;, , dbEncrypt + dbVersion20

Set dbsNorthwind = OpenDatabase(&quot;Nwind20.mdb&quot

' Show the properties of the compacted database.
With dbsNorthwind
Debug.Print .Name & &quot;, version &quot; & .Version
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> &quot;&quot; Then Debug.Print &quot; &quot; & _

prpLoop.Name & &quot; = &quot; & prpLoop
On Error GoTo 0
Next prpLoop
.Close
End With

End Sub


And others use there preferred version of compacting

Hope this helps
Hymn

 

[stickers]

There have been loads of posts by people a lot more knowledgable than me, but for what it's worth I had the same problem and was very alarmed, but find that compacting on close seems to more or less stop the growth. I think it is still getting bigger over time, and when it's used, but really slowly.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmm,

I thought I had replied here on the issue of compacting in &quot;Auto&quot; earlier ... hmmmmmmmmmmm ... perhaps NOT?


If the dbs are miultiuser apps, you will have trouble using any 'auto' version of compacting, as the methods will fail when there are additional users connected. AFAIK, this cannnot be overcome. The only way to get the db to compact is to use a routine to 'boot users', gain exclusive access, do the compact, release the db back to multiuse. In general, you also cannot get the majority of Win based back-up tools to back up a file with any (other) users attached. This can be hazzardous to your (db?) health, as most network 'admins' do not really bother to disconnect users before the back-up and few review the back-up log and notify the db (file) owner of any failures. So along comes little M. User who connects to your db and goes on vacation (literally or figuratively) for two weeks. After one week, Big M. User manages (oh-so-innocently) to utterly destroy your db, and that afternoon, YOU (db owner) are in the 'GREEN ROOM' awaiting summary execution ...




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hymn]

Perhaps
We are compacting a front end
Normally front end are on a individual desktop and most front ends are .mde's

And for the many people who close there machines and the majority network “admins“ who do their job correctly auto compacting of the db on their own desktop should be acceptable. The rest is an IF

I personally run a batch file at lunch time to copy my backend db’s, but that’s to make sure the workforce don’t lose half a day’s work

Plus 9 times out of ten I have a backup of my db's that have been distributed

Hope this helps
Hymn

 

[jrbarnett]

A couple of years ago, in developing an addin to document the objects within an Access database I found that there were several hiddent temporary tables/queries/forms/reports not showing up in the database window even if show hidden and system objects was turned on. These obviously were taking up space.
The best I could come up with was that they were created created as a result of:
a) saving before compacting
b) running uncompiled code (either in MDE format or tools -> compile all modules).

I presume they are also used to store undo information during editing of data and other objects.

You could see them with a quick DAO code routine along the lines of:

Dim tdf As Tabledef
Dim db As Database

Set db = CurrentDB
For Each tdf In db.TableDefs
   Debug.Print tdf.Name
Next
Set db = Nothing

They would disappear on compilation or compacting, but would reappear if changes were made.

John

 

[WB786]

Why not use the more simplier solution without any coding involved at all. Except for modifying the logon script of the users who use the database.

I have 200+ users using my database. The front end is an mde. Two files are copied to their c:\db folder: Mydb.mde and Mydb.mde.lnk.

The .lnk file (shortcut to the mde) is copied to their desktop and the mde stays on their c: drive. I don't put the mde on the desktop is because the users have roaming profiles so you don't want to upload the 20meg mde to their profile.

That's it. No need to ever compact the mde.

WB

 

[sohunter]

I have front-ends set (under the Options tab) to Compact on close. And they're not MDEs. And it seems to work. Does this suprise anybody? Should I really be making MDEs before I distribute?

The back-end tables file is the only one that I'd need to do manually, after making sure nobody was linked into it (it actually wont LET me compact, if anybody's connected, I think) and I've been meaning to write a procedure for the tables file to show me WHO's logged in. O'Reilly's Access Cookbook shows the code, but I think I've seen code posted here as well.

 

[PauloRico]

Just to add to the madness, I have noticed database bloat in some of our Access 97 databases, but not others. When investigated, it turned out that the databases that didn't bloat were on a read only (for the users) network drive, so they could not write to it to bloat it. Sounds logical.
But these databases work the same as if they were on a writable drive.
Does Access try to write something, realise it can't, and then just work anyway.

Another Access querk to keep us on our feet?

 

[Ed2020]

PauloRico,

Now that's a good idea! If I alter the properties of the frontend files to make them read-only that may well do the trick.

Have a purple star.

Ed Metcalfe.

Please do not feed the trolls.....