Compact a table-attached MDB

This is a follow-up from a PAQ where I ~thought~ I got the answer: I keep trying to compact a MDB whose tables are attached to a front-end. But a lock stays up to prevent my compacting it.

I've no problem with unattaching the tables AND closing all my forms before I get to work with a DBEngine.CompactDatabase(...) call, I even run a 'Doevents' call just in case. But oddly enough Access still gives me a 'User so-and-so (me) has already opened the database' or some such.
Now I really don't understand this: The files are on my local disk so network issues are moot, I'm the only user, there are no tables attached (anymore) and I've closed all my forms. Why can't I compact it then?!?

And a better question yet: What do I need to do to successfully compact that datafile from the front-end MDB?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Mach1proConnect With a Mentor Commented:
Sounds like either a form or some code in a form or your toolbar is still referencing the linked tables.  
Manually delete all the links to the back end database and then work with your forms to see if the ldb generates itself.  If you have code referencing it, then it will generate and the code is the culprit.  If you error out trying to open a form or toolbar, then those are Bound to the backend tables in some way and thats the problem.
you can not programmatically compact the database you are in from its own module or code.  The only way to compact the database you are in is through the menus (Tool>Database>Compact...)

If you want to use the .CompactDatabase method you must run that from another yes from the front end that would work (assuming the front end has none of the tables open at that time.

To compact from the front-end you need to do this:

    Dim dbsTemp As Database
    Set dbsTemp = OpenDatabase(sPath)
    If Dir("temp1.mdb") <> "" Then Kill "temp1.mdb"
    DBEngine.CompactDatabase sPath, "temp1.mdb"

this will compact it into 'temp1.mdb' in your temp'll have to rename it then...(don't remember the code offhand to do that.
francois_dionAuthor Commented:
Sorry, that doesn't work, it is exactly what I tried.

-I have closed all forms
-I have unattached the tables
-I use dbengine.compactDatabase from the front end, to compact the distant file.

It doesn't work. Something's still locked.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

have you tried to use the dbengine.compactdatabase from a blank database?  One that definitely has no links to the database you are trying to compact??
One thing you could do is detach your linked tables, compact the back end and then relink your table.  Here's a simplistic code you can modify for this purpose.

Dim db As Database
Dim strFileName As String
Dim td As TableDef
Dim strTable as String
strTable = "MyTable"
Set db = CurrentDb()

strFileName = "c:\my documents\databasename.mdb"
    DoCmd.DeleteObject acTable, strTable
     DBEngine.CompactDatabase (strFileName), ("c:\tempdata.mdb")
        Kill (strFileName)
        Name "c:\tempdata.mdb" As (strFileName)
   Set td = db.CreateTableDef(strTable)
   td.Connect = ";database=" & strFileName
   td.SourceTableName = strTable
   db.TableDefs.Append td
Did you try compacting it manually? Maybe your problem lies elsewhere.

francois_dionAuthor Commented:
To Mach1Pro: Nice try, but I stated clearly that I already:
-Close all the forms
-Unattach all the tables
-Use DBengine.CompactDatabase
Need glasses?  ^_-  Just kidding...

To MikeRenz and Oui_Li: There are no problems directly with the 'distant' MDB, I can compact it from a blank database, and I can compact it manually. The problem is that I can't use these solutions. I'll state again what I need to do:

I've an A97 front-end, it ~must~ be possible for it to compact a data mdb it connects to.  Problem: Even though I close forms and unattach tables, I'm still prevented.

Someone, please tell me why?
are you using bound forms/controls?  or are you opening your recordsets via code?
francois_dionAuthor Commented:
Both actually, that front-end I use is ~huge~ (20 megs) and works with all the attached tables in a variety of ways: Recordsets, bound subforms that change according to code, you name it.

I've even a toolbar that launches various operations. (Including that 'compact the datafile' I'm trying to build)
After detaching your tables, try deleting the associated .ldb file for the backend database before compacting.  
Hi francois_dion,

Here's one suggestion I've given earlier here (it's PAQ area now). Above function will compact the database to another database and then relink front end to compacted db and finally deletes previous database.

Function RunCompact()

Dim WorkDir As String
Dim CurrentDatabaseName As String
Dim TargetDatabaseName As String
Dim TableName(1000) As String
Dim tdf As TableDef
Dim i As Integer
Dim iCount As Integer

    ' define working directory
    WorkDir = "C:\"
    ' determine linked table source database
    For i = 0 To CurrentDb.TableDefs.Count - 1
        If CurrentDb.TableDefs(i).Attributes = dbAttachedTable Then
            CurrentDatabaseName = Mid(CurrentDb.TableDefs(i).Connect, 11)
            Exit For
        End If
    ' found any linked tables?
    If CurrentDatabaseName = "" Then
        MsgBox "No linked tables - operation interrupted"
        Exit Function
    End If
    ' the server database name is BEFile.mdb or BEFileA.mdb
    ' use own names here...
    If InStr(1, CurrentDatabaseName, "BEFileA") > 0 Then
        TargetDatabaseName = "BEFile.mdb"
        TargetDatabaseName = "BEFileA.mdb"
    End If
    ' Check if destination database already exists
    If Dir(WorkDir & TargetDatabaseName) <> "" Then
        If MsgBox("Destination database " & WorkDir & TargetDatabaseName & " already exists, do you want to delete it?", vbYesNo, "Confirm") = vbYes Then
            Kill WorkDir & TargetDatabaseName
            MsgBox "Compact procedure exiting"
            Exit Function
        End If
    End If
    ' Compact database
    DBEngine.CompactDatabase CurrentDatabaseName, WorkDir & TargetDatabaseName
    ' Check that new & compacted database file was created
    If Dir(WorkDir & TargetDatabaseName) <> "" Then

        With CurrentDb
            For i = 0 To CurrentDb.TableDefs.Count - 1
                ' link tables
                If .TableDefs(i).Attributes = dbAttachedTable Then
                    iCount = iCount + 1
                    TableName(iCount) = .TableDefs(i).Name
                End If
            ' drop existing linked tables and connect them into new compacted database
            For i = 1 To iCount

                ' drop table
                .TableDefs.Delete TableName(i)
                ' create new table and link it to new database
                Set tdf = .CreateTableDef(TableName(i))
                tdf.Connect = ";DATABASE=" & WorkDir & TargetDatabaseName
                tdf.SourceTableName = TableName(i)
                .TableDefs.Append tdf
        End With
        ' remove old database
        Kill CurrentDatabaseName
        MsgBox "Compacting operation failed, couldn't create new database file " & WorkDir & TargetDatabaseName
    End If
End Function

Hope this helps,
francois_dionAuthor Commented:
To Mach1Pro: Sorry, I end up with a 'permission refusée' when I try deleting the .ldb
I haven't the right to outsmart Access' basic file control, it seems.

To Paasky: Alas that doesn't help, I already have tried something similar, the problem is that dbEngine.CompactDatabase won't work with my distant file. I get a #3356 error.

Again, my question is more 'why' this is happening than 'how' to compact.
-I've unloaded the forms
-I've unattached the tables

Is it possible that some earlier (and completely unrelated) code managed to keep a lock up on my file because I didn't run a .Close method or something?

If .ldb file exists in your hard-drive that means someone is using/locking the database. You can't delete file because Access locks that file too. I very much suspect that you haven't closed all objects before you tried compacting and that's preventing opening in single user mode.

Here's the link you find utility that can be used to determine who's using database:

I believe that your .ldb file is the culprit. The file should automatically delete itself  once your connection to the database is lost.  You shouldn't even have to delete the tables.  If you can't delete the ldb file, you won't be able to compact the database. Make sure Access isn't open on the machine and then try deleting the file through explorer.  If you still can't delete the file, then you must have some kind of network or windows security on the folder that contains the ldb.
francois_dionAuthor Commented:
Paasky: The error message I get already tells me I'm the user who locks that DB.

Mach1: the ldb disappears fine when I exit the function after closing the form. The odd thing is, it won't vanish ~within~ the function nor let me work with the compact.

Starting with this, I've tried separating the functions that close the form and the one that compacts, no such luck: If I call them from the same event (a button click on a toolbar) the ldb sticks until Access is done with the button.

Now one of my questions is: With all forms closed, is it possible that forgetting an object.close somewhere in code (in a procedure that's long ended) could keep a lock on the mdb?

Thanks for the patience, guys...
francois_dionAuthor Commented:
Dang... Your request is the most sensible one, Mach1, alas since this is a 20 Meg front end, going through the whole starting code isn't something I can afford to spend time on...

The good news (on my side) is we've decided on a workaround: Before opening the file, I'll run a check that will read the MDB size, compare with a little data field in it (that saves the last compacted file size) and offer the possibility to compact the datafile ~before~ opening it. Then I'll have enough control over the file opening code to make it happen.

Thanks to everyone for trying, esp. to Mach1 for putting up with me. >:)

What's life without a few unsolved problems, after all?

s. Spacecat  ^n.n^
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.