I want to try to compact a database after I have deleted several tables and while the mdb is still open.

Some background to why I think I need to do this - I will be working with very large datasets and after the user selects the process to perform I am going to be exporting the result to excel and deleting the table(s) of calculations and attempt to compact the database so that the user can select another process without worrying about running out of space in the database.

Any assistance would help.

Dwight
Resource Analyst

Dwight,
you may want to check this out:
http://www.mvps.org/access/general/gen0013.htm

Steven

I am a loud man with a very large hat. This means I am in charge

Dwight,

The only option you have (from what it looks like to me) is to compact the database WITHOUT using code. I believe that in order to compact a database while it is still open, you need to go to Tools, Compact & Repair database




Ben

There was a discussion about this very topic last week on yahoo's AccessDevelopers group.

Duane Hookom pointed out "To compact the current database, I believe you must close it, open another, and compact the first. Michael Kaplan has a solution that Shut One Open New at
http://www.trigeminal.com/utility.asp?1033"

I myself use a shortcut icon with the /compact switch, but the database would have to be closed.

Another individual (Mike) posted the following:

I have seen many threads on this for answers. I haven't seen one
with CompactRepair so that's what I'm posting. It needs a reference
to Microsoft Scripting Runtime to use the FileSystemObject (my
preferred way of determining file existence). It takes a file and
functionally compacts it using the same name through a temp db.

Basically I compact "C:\db1.mdb" into "C:\db1_temp.mdb", check to
ensure the compact worked and created a file, delete "C:\db1.mdb",
then move "C:\db1_temp.mdb into "C:\db1.mdb".

Because of the DeleteFile method, if you choose to use this code, I
do not guarantee against loss, flooding, or spindling of your
database. But I do use it quite successfully on a daily basis. It
takes one parameter, the path to the database, i.e., "C:\db1.mdb"

Function CompactDB(strDB As String) As Boolean
' -- TAKES A DATABASE AND COMPACTS IT INTO ITS ORIGINAL NAME BY USING
A TEMP DB
On Error GoTo CompactDB_Err

Dim fso As New FileSystemObject
' -- COMPACT THE SOURCE INTO A TEMP DB
CompactDB = Application.CompactRepair(strDB, Left(strDB,
InStrRev(strDB, ".") - 1) & "_temp.mdb", True)
' -- IF SUCCESSFUL, DELETE THE SOURCE DB
If fso.FileExists(Left(strDB, InStrRev(strDB, ".") - 1)
& "_temp.mdb") Then
fso.DeleteFile (strDB)
End If
' -- RENAME THE TEMP TO THE ORIG
fso.MoveFile (Left(strDB, InStrRev(strDB, ".") - 1))
& "_temp.mdb", strDB

' RESET THE ERROR HANDLER
On Error GoTo 0
Exit Function

CompactDB_Err:
CompactDB = False
Exit Function
End Function

HTH,

Beth M
PS I also claim Mike's disclaimers :)

When performing a compact and repair on a database, you have to make certain that you have exclusive access to the database - no one else can be using it.

Rand