Solved

deleting records from database

Posted on 2000-03-03
10
129 Views
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!
0
Comment
Question by:majala
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 1

Expert Comment

by:pookytoo
Comment Utility
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.

0
 
LVL 15

Expert Comment

by:cquinn
Comment Utility
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!
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:vindevogel
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:Ask_ED
Comment Utility
Hi,

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
http://www.microsoft.com/SQL/productinfo/msdejet.htm


B) MSDE (Microsoft Data Engine) for Visual Studio 6.0
http://msdn.microsoft.com/vstudio/msde/default.asp


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
ED
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Accepted Solution

by:
MrXMrY earned 100 total points
Comment Utility
hi,
Pookytoo's answer is correct, but somehow we can cheat.
 The idea
   but all dirty thing's together.
 Stp1
   Create temp database.
 Stp2
   create temp tables.
   use SQL statement
 Stp3
   Del the temp database.
 
   It's simple, and it works

0
 

Author Comment

by:majala
Comment Utility
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.
0
 

Author Comment

by:majala
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:vindevogel
Comment Utility
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.
0
 

Expert Comment

by:MrXMrY
Comment Utility
hi,
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.
 Stp1
   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)
 
Stp2
   Create temp tables.
   Use SQL statement
dbsNew.Execute " CREATE TABLE  NewTable "   & "(FirstName TEXT, LastName TEXT, " _
                  & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
                  & "PRIMARY KEY);"
Stp3
   Del the temp databases.
Kill "c:\dir1\dir2\newdb.mdb"
I hope this help you. :)
I am sorry about my weak English. :}


0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now