Compact On Close: MS Access or Jet?

[Jamie Collins]

Does anyone know whether the Compact On Close option is limited to the
MS Access user interface (UI) or whether it applies to the Jet database
layer?

This relates to a question in Excel.Programming. The OP is not using MS
Access; rather, they are using ADO to connect to the .mdb database
layer. If the Compact On Close options has been set to on by Admin
using the MS Access UI, will closing the last ADO connection to the
..mdb trigger a compact?

My opinion, as a non-MS Access user, is Compact On Close applies to the
MS Access UI only, for the following reasons:

1) There is no corresponding setting in the OLE DB provider for Jet nor
in the Jet and Replication Objects (JRO) library.

2) The MS Access VBA reference tells me I may use GetOption/SetOption:

http://msdn.microsoft.com/library/d...wSettingOptionsFromVisualBasic_HV05188062.asp

All the other settings in the list seem to be MS Access-only i.e. no
corresponding settings in the OLE DB provider for Jet.

3) I think there would be too much overhead involved if this setting
did encompass all types of connection. When I compact 'manually' the
operation seems to take a relatively long time. It seems unreasonable
for a single ADO connection continually connecting/disconnecting to
incur this overhead.

Does anyone have any details? (The documentation always seems vague
when discussing Jet features; MS must enjoy blurring the distinction
between 'Jet' and 'Access'.) Alternatively, can anyone suggest a way of
testing whether terminating the last ADO connection causes the .mdb to
automatically compact?

Thanks,
Jamie.

--

 

[Nikos Yannacopoulos]

Jamie.

My guess is also 'No'. I suppose a simple test is to open an ADO
connection to a databaseand perform just a read operation / no change,
and then check the .mdb time stamp; if a compaction occurs it will be
set to current date/time, otherwise it will not.

Don't forget to post back your findings!

Regards,
Nikos

 

[Ken Snell [MVP]]

Jamie -

The Compact on Close operation occurs only when the file is actually opened
in ACCESS and then closed. Connections to the database, such as through
linking to tables, does not trigger a compact when the link is dropped. This
can be seen in a front end / back end split situation, where the backend
file will continue to grow until it's actually compacted via direct
open/close (with Compact on Close set), or via direct open and run the
Compact and Repair operation, or by programmatically running the VBA compact
operation through the code in another database file that is used for running
that code (such as a scheduled task that opens an ACCESS database with an
AutoExec macro that then runs code to compact the other database).

 

[Guest]

To expand on Ken's list:

Or, in absence of Access, by using the JetComp utility to compact the
database:

support.microsoft.com/default.aspx?scid=295334

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

 

[Guest]

Let me fix that paste job to make it a real link:

http://support.microsoft.com/default.aspx?scid=295334

Gunny

 

[Dirk Goldgar]

Jamie -

The Compact on Close operation occurs only when the file is actually
opened in ACCESS and then closed. Connections to the database, such
as through linking to tables, does not trigger a compact when the
link is dropped. This can be seen in a front end / back end split
situation, where the backend file will continue to grow until it's
actually compacted via direct open/close (with Compact on Close set),
or via direct open and run the Compact and Repair operation, or by
programmatically running the VBA compact operation through the code
in another database file that is used for running that code (such as
a scheduled task that opens an ACCESS database with an AutoExec macro
that then runs code to compact the other database).

Or by using the DBEngine.CompactDatabase method. In that case, though,
you have to handle the swapping/renaming of input and output database
files yourself, because the CompactDatabase method won't compact a
database onto itself. It's also my understanding that the Jet
CompactDatabase method doesn't do as thorough a job as the the Access
compact operation; however, that may not be significant for most
purposes.

 

[Ken Snell [MVP]]

Or by using the DBEngine.CompactDatabase method. In that case, though,
you have to handle the swapping/renaming of input and output database
files yourself, because the CompactDatabase method won't compact a
database onto itself. It's also my understanding that the Jet
CompactDatabase method doesn't do as thorough a job as the the Access
compact operation; however, that may not be significant for most
purposes.

This is the method to which I implied in my "last" option.

 

[Dirk Goldgar]

This is the method to which I implied in my "last" option.

I guess I misinterpreted your post. But you seemed to be talking about
opening an instance of Access to do the compact, and that's not
necessary if one uses the DAO DBEngine object and its CompactDatabase
method.

 

[Ken Snell [MVP]]

Quite true..in my personal example, I use an ACCESS database to run the
CompactDatabase method via VBA.

 

[Nikos Yannacopoulos]

Ken, Dirk,

For what it's worth, I've been using the DBEngine.CompactDatabase method
in a VB6.0 .exe for an overnight scheduled job for over a year, and
haven't noticed any difference between that and a manual Compact and
Repair from within Access; that is to say, on some occasions when I did
a manual one, the compacted BE size was exactly the same as the one
through the scheduled job.

Regards,
Nikos

 

[Jamie Collins]

Ken, Nikos, et al,

Many thanks for your input. I'm now convinced this is an MS Access-only
feature. The front end / back end split scenario was the clincher for
me.

Thanks again,
Jamie.

--

 

[Dirk Goldgar]

Ken, Dirk,

For what it's worth, I've been using the DBEngine.CompactDatabase
method in a VB6.0 .exe for an overnight scheduled job for over a
year, and haven't noticed any difference between that and a manual
Compact and Repair from within Access; that is to say, on some
occasions when I did a manual one, the compacted BE size was exactly
the same as the one through the scheduled job.

I think it would depend on what you do with the .mdb file, Nikos.
Here's an article from Michael Kaplan describing what I'm talking about.

http://www.trigeminal.com/usenet/usenet023.asp?1033
INFO: Another change in Access 2000: Jet Compact vs. Access Compact

Since he was talking about changes that came about in Access 2000, I
suppose it's possible that the article no longer applies, but I doubt
it.

 

[Nikos Yannacopoulos]

Dirk,

The article is very interesting, thanks for that! I am still on A2K I'm
afraid, so it applies to my case exactly. It also explains the behaviour
I've been seeing all along; my BE is just tables, so it makes no
difference according to the article.

Regards,
Nikos