Link to home
Start Free TrialLog in
Avatar of majala
majala

asked on

deleting records from database

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!
Avatar of pookytoo
pookytoo
Flag of Netherlands image

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.
Avatar of vindevogel
vindevogel

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.
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
ASKER CERTIFIED SOLUTION
Avatar of MrXMrY
MrXMrY

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of majala

ASKER

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.
Avatar of majala

ASKER

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