Software

Ask something
Question Locked

Auto Purging Database (Access)

0 helpful
7 replies

by zerinzafrin 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Dear Members and moderators,

I want to autopurge a database (developed by Access 2003 and Visual Basic of Visual studio 2005 in the front end) in two ways:

1. Default value ( one month)

2. Value set by User:

a) Selecting time from a dropdown list,

b) Selecting disk space to be free ( by % ).

Can anyone help me,please ?

Zerin

7 replies

by tony hopkinson

It you want automatic as in scheduled, you want a place to store how to purge.

Purge up to 1/6/2006 would only work once.

Purge one months data would be a disaster if you had an accident and it ran more than once.
I'd go for over x months/days old personally.

Same with a %age, don't get rid of 10% of your data, keep 90% of it.

Next problem is design. If it's just one table, you are okay, if it's several that are linked. Then you need to cascade the deletes. Not sure whether access does that.

So if you had order lines related to orders, get rid of the lines for the old orders then the old orders.

Space freed will be within the MDB, if it's disk space you are after you will have to compact as well. The latter means you need exclusive access as well, so must be done during down time. Also if you compact the %age idea will again suffer from potentiall deleting all you data if it repeats. Best bet there is instead of using the mdb file size, work out a magic number, 10,000 records = 10M or some such.

Calculating the space required can be done roughly by multiplying the number of records by the size of them. That you work out by looking at the column layout for each table.

Char(50) + 2 integers = 58 (or 108 if its' not unicode) for instance.
Blobs will complicate matters.

So once you;ve got the total size, then you need to count number of items to delete by months old for instance.

0 months old 10 *58 = 580 / total size = 6%
1 Months old 1000 ...

Then find the nearest to the target percentage to keep and run your delete query.

HTHs

As for the exact how of the code and the SQL, you'll have to be a bit more specific.

VB.net is not one of my skills the sql I can help with.

by zerinzafrin

Dear Tony Hopkinson,

I appriciate your help and quick reply.

What I want to do is...............deleting BLOB files and related data from a table.I have only one table in my database.So, deleting will be no problemm,as you said. But to relate the data from the table to a specific BLOB file is mattern of concern.I don't know how to do it and how to delete them both.Cause,they are related to each other.

SQL is ok.I only need the solution.I'm an OCP holder.So,I can understand your SQL and translate it into what I want to do.Can you send me codes..............if available and can be done by you?

Thanks in advance.

by zerinzafrin

Dear Tony Hopkinson ,

Sorry,I think I should add something =>

Actually,my database has one table only.But it also saves one .bmp image related to each row of the table in the database.Now,when the old data will be deleted..........it has to be deleted with the related image.I'm stucked into it.

As thousands of data will be receiving daily by this software,deleting old data is very important for me.Compressing files is not enough.Also,the operators are going to see reports all day long .............may be after each 10 minutes.So, in that case compressing files will create problems also.

If you can help me, please send me related codes or links.I have only 10 days at my hand to make it (my S/W ) work.

Regards,
Zerin Zafrin

by tony hopkinson

What databases do is store the blobs in one big internal file. Then in the table they store a pointer to it.
If you delete the record, the associated space for the blob will be marked free.

Works the same as your hard drive basically.
If you delete a file, what you are actually doing is deleting the reference, so that space can be reused.
Like your disk though, you get fragmentation problems. Blobs are contiguous, so if you delete an 8k image, and you want to store a 9k one the file will grow, and you are left with an 8k hole in it.

If you are deleting by time range, fragmentation overhead should be reduced as it's likely that all the blobs for all the months records are in the order they were added.

Start simple.
Set up a scheduled job on the machine where the access database it to run a batch file

Create a batch file to connect to the database and run a command in a file.

Then just try something like
Delete From MyTable where DateDiff('d',Now(), MyTable.DateAdded) > 200

200 is a number I plucked out of the air, you'll have to tune it. But if you schedule this daily it will get rid of anything more than 200 days old every time it runs.

%age wise.
I'm not sure I'd bother with initially.

Select Sum(DataSize(MyBlob) From MyTable
Will give you the total size of the bmps.
Do this after a compact and you should be able the rough estimate against the filesize of the mdb.
If the bmps are averaging the same size.

Select Year(DateCreated), Month(DateCreated), Sum(DataSize(MyBlob)) From MyTable Group By
Year(DateCreated), Month(DateCreated)
Works out the start/end of the month that equates roughly to the percentage you want to keep.

Plug that number of days in to the date query

Go for simple, you don't want people deleting your data willy nilly, your users don't want to be bothered with doing it.
Leave them all with a grin, that says hey the system's faster, Zerin's a clever guy.

by onbliss

Do you want to delete records from the tables, or do you want to compact?

It is MS Access's nature to bloat :-) Even if you delete records/objects. To effectively reclaim space, compacting is an effective technique.

If you are looking for compacting then you can set Access to compact as it closes. But if you want to compact it using VB.Net, then here is the solution from Microsoft: http://support.microsoft.com/default.aspx?scid=kb;en-us;306287

If you are looking to delete, then you have to consider several things Tony mentioned.

by zerinzafrin

Dear onbliss,

Thanks for your quick reply.I appriciate your help.I'm gonna look into the link you sent me and try it.Actually,my database has one table only.But it also saves one .bmp image related to each row of the table in the database.Now,when the old data will bedeleted..........it has to be deleted with the related image.I'm stucked into it.

As thousands of data will be receiving daily by this software,deleting old data is very important for me.Compressing files is not enough.Also,the operators are going to see reports all day long .............may be after each 10 minutes.So, in that case compressing files will create problems also.

If you can help me, please send me related codes or links.I have only 10 days at my hand to make it (my S/W ) work.

Regards,
Zerin Zafrin

This discussion is closed to new replies.