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.

--