deleting records from database

Posted on 2000-03-03
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.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


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 100 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

839 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