Automate compacting database

I used ADO to connect to an excel spreadsheet to an access database. Is
there a way to write a macro that will automatically compact the database
when the user closes the excel spreadsheet?

JJ,
There is a setting in Access that is "Compact on close".

NickHK

"JJ" <[email protected]> wrote in message
news:[email protected]...
> I used ADO to connect to an excel spreadsheet to an access database. Is
> there a way to write a macro that will automatically compact the database
> when the user closes the excel spreadsheet?

Thanks Nick, however I forgot to mention that the users do not open the
database. They only access an excel spreadsheet that allows them to read and
write to the database.

"NickHK" wrote:

> JJ,
> There is a setting in Access that is "Compact on close".
>
> NickHK
>
> "JJ" <[email protected]> wrote in message
> news:[email protected]...
> > I used ADO to connect to an excel spreadsheet to an access database. Is
> > there a way to write a macro that will automatically compact the database
> > when the user closes the excel spreadsheet?
>
>
>

JJ,
If you make this setting in the database once, it will compact each and
every time the (last-user) closes the database.

If you wish to compact each time through code:
http://www.freevbcode.com/ShowCode.asp?ID=1162

NickHK

"JJ" <[email protected]> wrote in message
news:[email protected]...
> Thanks Nick, however I forgot to mention that the users do not open the
> database. They only access an excel spreadsheet that allows them to read
and
> write to the database.
>
> "NickHK" wrote:
>
> > JJ,
> > There is a setting in Access that is "Compact on close".
> >
> > NickHK
> >
> > "JJ" <[email protected]> wrote in message
> > news:[email protected]...
> > > I used ADO to connect to an excel spreadsheet to an access database.
Is
> > > there a way to write a macro that will automatically compact the
database
> > > when the user closes the excel spreadsheet?
> >
> >
> >

NickHK wrote:
> > > There is a setting in Access that is "Compact on close".
> >
> > Thanks Nick, however I forgot to mention that the users do not open
the
> > database. They only access an excel spreadsheet that allows them
to read
> > and write to the database.
>
> If you make this setting in the database once, it will compact each
and
> every time the (last-user) closes the database.

I think the OP is correct i.e. this is an MS Access setting which is
unavailable to them because they are not using MS Access. If it was a
setting in the database layer there would be a corresponding property
in the Jet provider, yet I cannot find such a property.

Jamie.

--

Jamie,
Assuming he's the Admin, he could make the setting as he would access to the
DB.
If the users are do not have that permission, they would not be able to
compact anyway.

NickHK

"Jamie Collins" <[email protected]> wrote in message
news:[email protected]...
>
> NickHK wrote:
> > > > There is a setting in Access that is "Compact on close".
> > >
> > > Thanks Nick, however I forgot to mention that the users do not open
> the
> > > database. They only access an excel spreadsheet that allows them
> to read
> > > and write to the database.
> >
> > If you make this setting in the database once, it will compact each
> and
> > every time the (last-user) closes the database.
>
> I think the OP is correct i.e. this is an MS Access setting which is
> unavailable to them because they are not using MS Access. If it was a
> setting in the database layer there would be a corresponding property
> in the Jet provider, yet I cannot find such a property.
>
> Jamie.
>
> --
>

NickHK wrote:

> Assuming he's the Admin, he could make the setting as he would access
to the
> DB.

If you are sure this setting applies to the MS Jet database layer and
not the MS Access UI, then please post the details here.

Many thanks,
Jamie.

--

Jamie,
Under Option>General, check the Compact On Close.
As far I know it's a database specific and presumably could be changed by
anyone with Administer rights to the DB, maybe only if they have it open
exclusively.
The Help is rather sparse on the matter.

NickHK

"Jamie Collins" <[email protected]> wrote in message
news:[email protected]...
>
> NickHK wrote:
>
> > Assuming he's the Admin, he could make the setting as he would access
> to the
> > DB.
>
> If you are sure this setting applies to the MS Jet database layer and
> not the MS Access UI, then please post the details here.
>
> Many thanks,
> Jamie.
>
> --
>

NickHK wrote:
> Under Option>General, check the Compact On Close.
> As far I know it's a database specific and presumably could be
changed by
> anyone with Administer rights to the DB, maybe only if they have it
open
> exclusively.

Thanks for this. Now I have to think of a way to test whether the file
is in fact being compacted. Suggestions welcome.

Meanwhile, I'd like to press you on this: why do you think this is a
setting in the Jet database layer? Out of fairness, I'll tell you why
I think it isn't:

1) If the setting was in the database layer I would expect EITHER the
OLE DB provider OR the Jet and Replication Objects (JRO) to expose the
setting as a read/write property. I cannot find such a setting.

What I *can* find is that one may use the GetOption and SetOption
methods of the MS Access Application object to get/set the Compact On
Close option:

http://msdn.microsoft.com/library/de...HV05188062.asp

Looking at the descriptions in the list, they all seem to refer to
settings in the MS Access UI that have no corresponding settings in the
Jet OLE DB provider.

2) I can write ADO code such as this:

Dim Con As Object
Dim i As Long
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = MY_JET_CONN_STRING
For i = 1 to 100
.Open
.Execute "INSERT INTO MyTable VALUES (1);"
.Close
Next
End With

Assuming I am the only user of the database, I would be surprised if
there was a database level setting which would result in the file being
compacted each time I closed my connection.

When I compact my database using JRO it takes a relatively long time
and I end up with two files i.e. the original and the newly compacted
file. When I do the same using the tools in the MS Access UI I end up
with just one file but it takes a bit longer because it has to close
then reopen the original file, presumably because it gets overwritten
using the compacted copy.

I tried the above ADO code on a recently compacted mdb, first with
Compact On Close set off, then with it on. I could not detect any
significance difference in execution time to suggest the file was being
compacted 100 times in the process.

Jamie.

--

Jamie,
Can't answer on most of what you are asking at the moment, but Compact will
fail if you are not the only person logged in.
I imagine if you try and log in during a Compact, you will not succeed.

Check out the Object browser for compact; it give some info on it place in
the model.

NickHK


"Jamie Collins" <[email protected]> wrote in message
news:[email protected]...
>
> NickHK wrote:
> > Under Option>General, check the Compact On Close.
> > As far I know it's a database specific and presumably could be
> changed by
> > anyone with Administer rights to the DB, maybe only if they have it
> open
> > exclusively.
>
> Thanks for this. Now I have to think of a way to test whether the file
> is in fact being compacted. Suggestions welcome.
>
> Meanwhile, I'd like to press you on this: why do you think this is a
> setting in the Jet database layer? Out of fairness, I'll tell you why
> I think it isn't:
>
> 1) If the setting was in the database layer I would expect EITHER the
> OLE DB provider OR the Jet and Replication Objects (JRO) to expose the
> setting as a read/write property. I cannot find such a setting.
>
> What I *can* find is that one may use the GetOption and SetOption
> methods of the MS Access Application object to get/set the Compact On
> Close option:
>
>
http://msdn.microsoft.com/library/de...us/vbaac11/htm
l/achowSettingOptionsFromVisualBasic_HV05188062.asp
>
> Looking at the descriptions in the list, they all seem to refer to
> settings in the MS Access UI that have no corresponding settings in the
> Jet OLE DB provider.
>
> 2) I can write ADO code such as this:
>
> Dim Con As Object
> Dim i As Long
> Set Con = CreateObject("ADODB.Connection")
> With Con
> .ConnectionString = MY_JET_CONN_STRING
> For i = 1 to 100
> .Open
> .Execute "INSERT INTO MyTable VALUES (1);"
> .Close
> Next
> End With
>
> Assuming I am the only user of the database, I would be surprised if
> there was a database level setting which would result in the file being
> compacted each time I closed my connection.
>
> When I compact my database using JRO it takes a relatively long time
> and I end up with two files i.e. the original and the newly compacted
> file. When I do the same using the tools in the MS Access UI I end up
> with just one file but it takes a bit longer because it has to close
> then reopen the original file, presumably because it gets overwritten
> using the compacted copy.
>
> I tried the above ADO code on a recently compacted mdb, first with
> Compact On Close set off, then with it on. I could not detect any
> significance difference in execution time to suggest the file was being
> compacted 100 times in the process.
>
> Jamie.
>
> --
>

NickHK wrote:
> Compact will
> fail if you are not the only person logged in.
> I imagine if you try and log in during a Compact, you will not
succeed.

NickHK,
Thanks for the tip but I'm not sure how I can exploit it. For example,
if I disconnect the last user, immediately try to reconnected and
succeed, does this mean the file was not compacted, or that it finished
compacting really quickly, or that my request to connect was queued
until the compact finished, etc?

Jamie.

--

I posted the question in microsoft.public.access under the heading
'Compact On Close: MS Access or Jet?':

http://tinyurl.com/3nxlc

The conclusion seems to be this setting only applies when the .mdb etc
is opened/closed using the MS Access UI.

Jamie.

--

Jamie,
I was a good idea to there, as those correspondents are certainly more
knowledgeable than me.

NickHK

"Jamie Collins" <[email protected]> wrote in message
news:[email protected]...
> I posted the question in microsoft.public.access under the heading
> 'Compact On Close: MS Access or Jet?':
>
> http://tinyurl.com/3nxlc
>
> The conclusion seems to be this setting only applies when the .mdb etc
> is opened/closed using the MS Access UI.
>
> Jamie.
>
> --
>