Go Premium for a chance to win a PS4. Enter to Win


deleting records from database

Posted on 2000-03-03
Medium Priority
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!
Question by:majala
  • 2
  • 2
  • 2
  • +4

Expert Comment

ID: 2579256
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.

LVL 15

Expert Comment

ID: 2579331
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!
LVL 18

Expert Comment

ID: 2579356
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 2579668
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.

Expert Comment

ID: 2582003

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

Accepted Solution

MrXMrY earned 300 total points
ID: 2584450
Pookytoo's answer is correct, but somehow we can cheat.
 The idea
   but all dirty thing's together.
   Create temp database.
   create temp tables.
   use SQL statement
   Del the temp database.
   It's simple, and it works


Author Comment

ID: 2586748
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.

Author Comment

ID: 2586756
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.

Expert Comment

ID: 2586883
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.

Expert Comment

ID: 2599865
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. :}


Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

886 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