Solved

Compact a table-attached MDB

Posted on 2000-05-03
16
525 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:francois_dion
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 3

Expert Comment

by:MikeRenz
Comment Utility
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 database...so 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 directory...you'll have to rename it then...(don't remember the code offhand to do that.
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:MikeRenz
Comment Utility
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??
0
 
LVL 6

Expert Comment

by:Mach1pro
Comment Utility
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
0
 
LVL 2

Expert Comment

by:oui_li
Comment Utility
Did you try compacting it manually? Maybe your problem lies elsewhere.

Will
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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?
0
 
LVL 3

Expert Comment

by:MikeRenz
Comment Utility
are you using bound forms/controls?  or are you opening your recordsets via code?
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Expert Comment

by:Mach1pro
Comment Utility
After detaching your tables, try deleting the associated .ldb file for the backend database before compacting.  
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
    Next
         
    ' 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"
    Else
        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
        Else
            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
            Next
             
            ' 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
            Next
             
        End With
         
        ' remove old database
        Kill CurrentDatabaseName
    Else
        MsgBox "Compacting operation failed, couldn't create new database file " & WorkDir & TargetDatabaseName
    End If
     
End Function

Hope this helps,
paasky
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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?
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
francois_dion,

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:

http://support.microsoft.com/support/kb/articles/Q186/3/04.ASP?LN=EN-US&SD=gn&FR=0

Regards,
Paasky
0
 
LVL 6

Expert Comment

by:Mach1pro
Comment Utility
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.
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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
0
 
LVL 6

Accepted Solution

by:
Mach1pro earned 100 total points
Comment Utility
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.
0
 
LVL 1

Author Comment

by:francois_dion
Comment Utility
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^
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now