Jet database does not reuse disk space of deleted tables

Thank you angelIII.

I have tried to compress the database as described in the above link, but get the error, "You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'PEDRO'.  Try again when the database is available".

I have called myconnection.Close before calling CompactDatabase, but it does not help. What else shall I do to close the database?

with access it is difficult to say who is currently connected to it... at least I don't know a way.

There are no users but the current program.

copy the file into another folder - compact the mdb from the new folder. Then overwrite the old file with the newly compacted one.

HTH
~BC

If that does not help, you might create a pointer q in the Microsoft Access topic area:
https://www.experts-exchange.com/Databases/MS_Access/

How do I create a pointer in another topic area?

I created the pointer for you this time, so you see how this is done:
https://www.experts-exchange.com/questions/21793127/Pointer-shrink-Access-DB-with-VB-Net.html

Thanks

have u seen this? does this help?
http://www.knowdotnet.com/articles/compactmdb.html

For the benefit of others who didnt read the link I gave (or perhaps is now expired)
This is what it says


How do I compact and repair a Microsoft Access Database (.MDB) when using ADO.NET?  ADO.NET does not support the compacting and repairing of an Access Database, so the only answer that I have found is to drop back to DAO (remember DAO, back before ADO!)

For all of the great new features of ADO.NET, it does not support the compacting and repairing of an MDB.  So, we simply go back and include DAO in our project and use it just for the needed functionality.

First, include a reference to DAO360.DLL in you project references.  Then call something like the VB.NET method shown below.


   Private Sub mnuRepairCompact_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles mnuRepairCompact.Click
      Dim db As DAO.DBEngine
      Dim sUFN As String
      Dim sFN As String = _
         System.IO.Path.GetFileNameWithoutExtension(DatabaseName)

      Try
         sUFN = AppPath & "\" & sFN & Format(Now, "MMddyyyyHHmmss") & _
            ".mdb"
         Rename(DatabaseName, sUFN)
         db = New DAO.DBEngine()
         db.CompactDatabase(sUFN, AppPath & "\" & sFN)

      Catch ex As System.Exception
         StructuredErrorHandler(ex)
      End Try
   End Sub
 

To compact the database, try these two methods:

For both methods, check these first:
Make sure no one has the database open
Right click on the task bar, select task manager. If MS Access shows up on the applications tab or processes tab, end it.
Go to the folder containing the database. If a file with the same name as your database exists but with an .ldb extension, delete it.  You may need to reboot to delete the .ldb file.

Open Ms Access. Goto file, open. Select your database. Click on the down arrow to the right of the open button.  Select open exclusive.
Goto tools, database utilities, compact and repair.

Use jetcomp.exe  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956

Another option is to set the Database Property "Compact on closure" in the Tools/Options.
Thus the closure will always trigger the compact what can be annoying :-)

Nic;o)

To thenelson and nico5038:

The application is supposed to run on a computer without Access, and I need to compact the DB in run-time. Thus the question is, what shall the application do to release the DB, so that jro.CompactDatabase could work? As I wrote, myconnection.Close is apparently not enough.

>The application is supposed to run on a computer without Access
In that case, use jetcomp.exe.  You will need to close the .net application before running jetcomp.exe.

Not good. It must be done within the appplication, transparently for the user.

Won't happen.  For compact to work on the database, the database must be closed down and not accessed by any application.  If jetcomp.exe works for you, you can shell from your application to a batch file that runs jetcomp after a delay and close your net application.  The batch file can reopen the net application after jetcomp is done.  Or you can use Windows scheduler to shut down the net application, run jetcomp and restart the net application at night.

Are you telling that there is no way to close the database _within the application_, run jro.CompactDatabase and then reopen the database?

I do not know VB.net.  I came from the Access link.  I would try running jetcomp.exe with your VB.net application shut down to see if it works.  If that does work, I would then try running jetcomp.exe after you call myconnection.Close.  If that works, you're home free.  Call myconnection.Close, shell to jetcomp.exe, then reestablish the connection.  You can then play with jro.CompactDatabase or rockiroads mnuRepairCompact to see if you could get one of them working.  If myconnection.Close, jetcomp does not work, then you know you need to find something else than myconnection.Close to allow any compression utility to have exclusive access.

Can't you copy the database to a new file? Compact it from there, rename your current file, and move the newfile to the original file name.

If works with executables in use, might work with databases as well.

It's all too heavy. All I want is to be able to rewrite some tables, without inflating the DB. It works with ADO in VB6, but for some reason not with ADO.NET

hey karnovsk, just out of curiosity, did u try my suggestion? Im assuming you did and you suffered the same fate, since the DB is open

that first post, I saw the same thing from the MS site http://support.microsoft.com/default.aspx?scid=kb;en-us;306287
little more info on references if that helps

What if you split the AccessDB into a frontend/backend use. Frontend can be distributed to the various users and this contains the linked tables to the Access backend. Im not sure if this creates a lock for the backend one, it may do but it could be an idea. Your code would then just operate the backend one.

Another idea, what if you created a job which compacts the DB (which you already the code for now),
then use Windows Scheduler to run, say a weekly job in the middle of the night or some other time when u know people are not in.
It may not be ideal and what you want, but if all else fails, it could be an alternative

Logging is required, you can check for the existence of a .ldb file. If it exists then assume someone has left it open

I have did some more research, and the answer is yes, you can close the database from within the application.

Normally, myconnection.Close is enough. In my application,it did not work because I had also an ADOX catalog open.

After I have removed it, jro.CompactDatabase worked like a charm.