Jet database does not reuse disk space of deleted tables


My application uses ADO.NET with a Jet database (Provider=Microsoft.Jet.OLEDB.4.0).

From time to time, I have to drop some tables and build them anew. Each time, the database grows larger, apparently meaning that the disk space of deleted tables is not reused.

What can I do to prevent the database from growing?

I delete a table using the following code:

            Dim dbcDelete as OleDbCommand = _
                                  New OleDbCommand("DROP TABLE tablename", myconnection)

I build a table  using the following code:

            Dim strQuery As String = _
                                "CREATE TABLE tablename (" & _
                                    "Id INTEGER CONSTRAINT PrimInd PRIMARY KEY, " & _
                                    "Surname CHAR, " & _
                                    "GivenName CHAR)"
            Dim dbcCreate As OleDbCommand = New OleDbCommand(strQuery, myconnection)

Note: Instead of dropping/creating tables, I have tried to delete all table rows, but it did not help: the dabase grew all the same.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot prevent, you can only compress the database from time to time:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karnovskAuthor Commented:
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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with access it is difficult to say who is currently connected to it... at least I don't know a way.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

karnovskAuthor Commented:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
If that does not help, you might create a pointer q in the Microsoft Access topic area:
karnovskAuthor Commented:
How do I create a pointer in another topic area?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I created the pointer for you this time, so you see how this is done:
karnovskAuthor Commented:
have u seen this? does this help?
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 = _

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

      Catch ex As System.Exception
      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;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 :-)

karnovskAuthor Commented:
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.
karnovskAuthor Commented:
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.
karnovskAuthor Commented:
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  I came from the Access link.  I would try running jetcomp.exe with your 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.
karnovskAuthor Commented:
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;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

karnovskAuthor Commented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.