MDB bloat guess 1

[SBerthold]

How can I determine if an MDB needs compacting?
Is it just a guessing game?

 

[MichaelRed]

It should be possible to come close to the INCREMENTAL size an MDB should reflect -IF (and ONLY if) you are able to keep track of every database (table) transaction. since the overhead for this would be considerably in excess of the actual compact routine, I know of no attempt to actually do it. Mostly it is done on a regularly schedualed basis, with the interval determined by the dba.


On the other hand, if a db really ineeds to be compacted more often than weekly, you are probably either doing something 'bad' or are using the wrong db engine.

Ms. A, like most of the less than 'Industrial strength' db engines, doesn't actually "delete" records (or anything else) untill the compact process is executed, and it "deletes" them then by copying all of the 'active' objects in the source db to a (new) target db. If you have a lot of records and a lot of transactions on the records, it is likely that the db will "bloat" to the point of impacting performance within just a few days. If this is occuring, it is probably time to move on (to the "Industrial Strength" db engine).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

Hmmmmmmm,

You can easily get a rough estimate (not always 100% correct...but a good estimate).

Check the return value of the "Jet OLEDB:Compact Reclaimed Space Amount" Parameter.

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[MichaelRed]

Jet OLEDB:Compact Reclaimed Space Amount" Parameter. O.K. this is new )and news) to me. I didn't find it (or even parts of it) in either help or the object browser. How about a BROAD hint to look it up somewhere?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

It cannot be found in the object browser because it is not a property of ADO, but of the native JET db manager, and, because it is provider specific, it can only be used with a JET db, and therefore is not included as a ADO property/method.

In the VB help, and MSDN you can find the Properties for the native JET db manager under Appendix B: Microsoft Jet 4.0 OLE DB Properties Reference

Pass it as an argument of the Properties collection of a ADO connection object after establishing connection to the MS JET MDB via the JET db provider.

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

You can find out the properties of any provider, which (not all) are revealed to ADO, by looping through the Properties collection of the connection object, having the Name property of the property returned.

For i = 0 to con.Properties.Count - 1
Debug.Print con.Properties(i).Name
Next i [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[SBerthold]

Excellent!
I guess you gotta know your stuff for things like this!