compact MS Access while list box connected to database in spreadsheet

[dim1ann]

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

 

[Robin Hammond]

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