compact MS Access while list box connected to database in spreadsheet

hi,
im using listboxes in my spreadsheet to show data (using ADO) saved in
access database..listbox updates on open_ excel event...
on the same spreadsheet i have a routin to compact that database which
i'm retriving dates from ..
it seems that the spreadsheet keeps the connection with database ( .ldb
file is on) which prevents the routine to compact database.. with err
message that admin has that db opened..

any help or direction to solve the issue?
thanks
Dim

Dim,

You need to use a disconnected recordset at the client by the sounds of it.

Immediately after you load a recordset it should be as easy as

Set rsTables.ActiveConnection = Nothing

You can still make changes to the recordset at the client, re-establish a
connection, use UpdateBatch, etc., but if you do this, make sure you reload
the recordset from the db again afterwards.

Robin Hammond
www.enhanceddatasystems.com

<[email protected]> wrote in message
news:[email protected]...
> hi,
> im using listboxes in my spreadsheet to show data (using ADO) saved in
> access database..listbox updates on open_ excel event...
> on the same spreadsheet i have a routin to compact that database which
> i'm retriving dates from ..
> it seems that the spreadsheet keeps the connection with database ( .ldb
> file is on) which prevents the routine to compact database.. with err
> message that admin has that db opened..
>
> any help or direction to solve the issue?
> thanks
> Dim
>