Hi Everyone,

For a number of reasons, I have created an excel workbook that imports an access table. My VBA does the following

1. Open an access database containing one table
2. Export ranges from excel into access to create additional tables
3. Perform queries to create temporary tables
4. Export the end table
5. Delete all the temporary tables, leaving behind only the original table


This creates a fair amount of bloat in my file size and eventually, if not compacted and repaired, runs up against the 2 gb limit. To combat this, I set the access database to compact on close. This worked for a while, but now when I close the database, it give me an error message that the file is already in use. I have also tried opening the database exclusively, but to no avail. Lastly, I am the only user and this is not hosted on a shared network. An example of my code is below.
Sub importdata()

dim myfile as string
dim acc as New Access.Application
dim rs as recordset
dim ws as worksheet

set ws= thisworkbook.sheets(1)
With Application.FileDialog(msoFileDialogFilePicker)
    .Show
    .AllowMultiSelect = False
    myfile = .SelectedItems(1)
End With

acc.OpenCurrentDatabase myfile, True
    acc.UserControl = True

'run queries and create temporary tables

set rs=acc.currentdb.openrecordset(mytable, dbopensnapshot)
with ws
    Range("A2").copyfromrecordset, 50
end with
rs.close
set rs=nothing

'delete temporary tables

acc.Application.SetOption "Auto Compact", True
acc.closecurrentdatabase
acc.Quit

End sub

When I replace acc.closecurrentdatabase with acc.currentdb.close, I do not get an error, but the compacting does not work. Can anyone please advise?