We help IT Professionals succeed at work.

Jet database does not reuse disk space of deleted tables

karnovsk
karnovsk asked
on
Medium Priority
382 Views
Last Modified: 2011-09-20
Hello,

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)
            dbcDelete.ExecuteNonQuery()

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)
            dbcCreate.ExecuteNonQuery()

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.


Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
you cannot prevent, you can only compress the database from time to time:

http://www.computing.net/programming/wwwboard/forum/13058.html

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
There are no users but the current program.

Commented:
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
How do I create a pointer in another topic area?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I created the pointer for you this time, so you see how this is done:
http://www.experts-exchange.com/Databases/MS_Access/Q_21793127.html

Author

Commented:
Thanks
CERTIFIED EXPERT
Top Expert 2006

Commented:
have u seen this? does this help?
http://www.knowdotnet.com/articles/compactmdb.html
CERTIFIED EXPERT
Top Expert 2006

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

CERTIFIED EXPERT

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

Commented:
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)

Author

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.

CERTIFIED EXPERT

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

Author

Commented:
Not good. It must be done within the appplication, transparently for the user.
CERTIFIED EXPERT

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

Author

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

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

Author

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
CERTIFIED EXPERT
Top Expert 2006

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


Author

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.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.