We help IT Professionals succeed at work.

deleting records from database

majala asked
Last Modified: 2010-05-02
I use vb6 and its professional edition.

My program adds records to database (Access-database) and almost immediately deletes these same records. If I do this many times, size of database increases very much. Databases size is about 70 kb when it's empty and compacted but when I add there record and then delete it about hundred times size could be something like 500 kb. When I close my program I compact that database and size of it is again that 70 kb.

Problem is that my program could be running many weeks or months without closing and I have to compact that database more often than in close event. Is there any other way to add and delete records from database so that size of it don't increase too much (other that compacting database at the same time)? What is the most efficient way to handle this kind of problem? Is this a normal databases behavior? I don't mind if you give all kind of information related to this problem?

Thanks in advance!
Watch Question

Hi majala,

This behavior is normal behavior for an Access database and can't be changed.
The reason that this happens is that Access doesn't delete the records realy. It just places a flag that the record is deleted. A sort of placing things in your garbage bin in windows. When you compact, all records that are flagged deleted, are removed. Like emptying the garbage bin in windows.

You can use on of the following possibilities:
1) Compact the database in code.
   You can do that after a certain amount of deletes (with a counter) or after a certain amount of time (with a timer).
   The command for compacting the database in code would be: dbengine.compactdatabase.
   This command is part of the DAO library, so you have to reference that (you probably have that referenced).
   However, you have to close the database in code befor you can compact it, then compact it and the reopen it.

2) You can switch to SQL server.
   There is an upgrade wizard which can create an SQL database from your Access database. If you have the enterprise version of VB, you have a developer edition of SQL server on the VB CD to try.

How about doing it a different way - don't physically delete the records, but add a boolean field which acts as a deletion marker - set it to true if the record can be overwritten, false if you need to keep it for the moment.  When adding new records, bring back a recordset containing the logically deleted records using an SQL query.  If there are records in the recordset, change their contents and save them.  If there are no records in the recordset, add new records and save them.  Periodically you could run a query to remove your logically deleted records and then do a compact.
The base size of the database may be a bit larger, but it shouldn't grow too much.

Just a lateral thought, but it might work!

cquinn's answer is the way to go.  When you delete a record make it

rs!active = false

when selecting records using sql you need to do '..where active = true and (....

when creating a new record do a rs.findfirst "active = false" then use that record as the new record by updating it.  Only if you don't have an existing record with rs!active = false need you do an addnew.

The amount of work and testing depends on the size of your project.
Use one of the above answers ... they're correct.

Make a table with the number of records you need (max. you need), flag them and update the data when you need.

This way, your database will not increase.


Pookytoo's answer is correct, the others would not decrease the size of the database during runtime and only add an extra step of deletion for all records with the active flag set to false on closure before the compact occurs.

I would add that Microsoft has added a new type of database engine that sits between Access and a full MS-SQL server in terms of marketing position, it's called MSDE and it is meant for desktop use and for easier migration to MS-SQL when and if required, ( not to be confused with 'SQL Server Desktop' which requires client licenses for use). While the join statements within your code would have to be rewritten you will find this a much more robust solution and the Database would never need to be compacted !

For more information on MSDE try:

A) Choosing between MSDE and Jet

B) MSDE (Microsoft Data Engine) for Visual Studio 6.0

Please also note that Access still has a 1 gig file size limitation and it takes hours and hours to compact one of those databases :(

Hope this helps
This one is on us!
(Get your first solution completely free - no credit card required)


Sorry, but I don't quite understand what you mean about creating database and tables and then deleting them. Why should I do this every time I add record and then delete it?
Pookytoo adviced me that dd and delete records from database like I normally do(which is always the same database) but I have to compact it after certain amount of time or deletes.

Could you explain me a little bit more.


Sorry, but I don't quite understand what you mean about creating database and tables and then deleting them. Why should I do this every time I add record and then delete it?
Pookytoo adviced me that I add and delete records from database (which is always the same database) like I normally do but I have to compact it after certain amount of time or deletes.

Could you explain me a little bit more.
Ask_ED: Our solution does not decrease the size of the database, it only ensures that the size does not get bigger.

We even would not delete the flagged records. Just leave them there for later use.

First it's not the efficient way.
Second: you don't have to delete the the temp database after every records delete, put it as menu item, command button - tell the user chose this option (deleting database) daily, monthly ...  
The idea put all dirty things together.
   Create temp database.
   Use CreateDatabase method to create a new Database object.
      Dim wrkDefault As Workspace
      Dim dbsNew As Database
      ' Get default Workspace.
      Set wrkDefault = DBEngine.Workspaces(0)
      ' Create a new database
      Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", dbLangGeneral)
   Create temp tables.
   Use SQL statement
dbsNew.Execute " CREATE TABLE  NewTable "   & "(FirstName TEXT, LastName TEXT, " _
                  & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
                  & "PRIMARY KEY);"
   Del the temp databases.
Kill "c:\dir1\dir2\newdb.mdb"
I hope this help you. :)
I am sorry about my weak English. :}

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

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


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.