Solved

Compact a table-attached MDB

Posted on 2000-05-03
16
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2773714
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
ID: 2774057
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
ID: 2774906
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 6

Expert Comment

by:Mach1pro
ID: 2776000
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
ID: 2776827
Did you try compacting it manually? Maybe your problem lies elsewhere.

Will
0
 
LVL 1

Author Comment

by:francois_dion
ID: 2776968
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
ID: 2777308
are you using bound forms/controls?  or are you opening your recordsets via code?
0
 
LVL 1

Author Comment

by:francois_dion
ID: 2777418
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
 
LVL 6

Expert Comment

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

Expert Comment

by:paasky
ID: 2778117
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
ID: 2778254
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
ID: 2778316
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
ID: 2779099
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
ID: 2780609
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
ID: 2780781
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
ID: 2781628
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

739 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