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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
ups.com...

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 OptionGeneral, 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" wrote in message
oups.com...

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 OptionGeneral, 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" wrote in message
oups.com...

NickHK wrote:
Under OptionGeneral, 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" wrote in message
ups.com...
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.

--