Auto Purging data

[Zerin]

To all,

Best regards.I have a problem.If anyone has any solution,please reply as soon as possible.

Is aouto purging possible in Access? How can I get related codes?

Zerin

[MMcCarthy]

To all,

Best regards.I have a problem.If anyone has any solution,please reply as soon as possible.

Is aouto purging possible in Access? How can I get related codes?

Zerin

Hi Zerin,

What are you trying to purge?

Mary

[NeoPa]

If you are trying to purge unused data from your database this function should work for you.

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "") As Boolean
    Dim strNewDB As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting """ & strDB & """.")
    If strPW <> "" Then strLocale = ";pwd=" & strPW
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function

If not, then please explain your question more clearly so we can help you.

[Zerin]

Thanks for your reply,

Actually,I developed a software using Visual Studio 2005 (by VB code).It uses Access database.Now,I want to delete data automatically after a month or two or any option given by the operator.

Can it be done? Please help me. Thanks in advance.

Zerin

[MMcCarthy]

Thanks for your reply,

Actually,I developed a software using Visual Studio 2005 (by VB code).It uses Access database.Now,I want to delete data automatically after a month or two or any option given by the operator.

Can it be done? Please help me. Thanks in advance.

Zerin

Zerin where are the option selections being stored. If they are in a table on their own then.

Dim strSQL As String
 
   strSQL = "DELETE * FROM TableName;"
   DoCmd.RunSQL
 
 

should do the job.

Mary

[Zerin]

Dear mmccarthy ,

Thanks for your reply.Actually,I don't have any idea where to set the options. As far as I can understand,the delete method should find out the old data first and then delete them.Now, my software's requirement is that..............

1. The default value will be 1 month ( that means,identify first the old data and then delete every month's last day)

2. User choice should be given to select autopurging policy:

a) related to time ( Drop down list for selecting month)

b) related to disk space ( free 50% memory space or more)

The problem is,I can't understand where to add the modules...................in the database (Access 2003) or in the front end view (Visual Basic 2005 ) ? Actually,I don't know from where to start.

The database is working well.Only auto purging feature has to be added. Please help me.

Thanks in advance.

Zerin

[Zerin]

Dear NeoPa ,


Thanks for your reply.Actually,I don't have any idea where to set the options will be better. As far as I can understand,the delete method should find out the old data first and then delete them.Now, my software's requirement is that..............

1. The default value will be 1 month ( that means,identify first the old data and then delete every month's last day)

2. User choice should be given to select autopurging policy:

a) related to time ( Drop down list for selecting month)

b) related to disk space ( free 50% memory space or more)

The problem is,I can't understand where to add the modules...................in the database (Access 2003) or in the front end view ( Visual Basic 2005 of Visual Studio 2005 ) ? Actually,I don't know from where to start.

The database is working well.Only auto purging feature has to be added. I tried your code but may be the version is different.Please help me.

Thanks in advance.

Zerin

[Zerin]

Dear Neopa,

I tried the code in module with my Access database but it gives compilation error like: "Sub or Function not defined " for the following line of code:

If Exist(strNewDB) Then Kill strNewDB

the full code you sent me was:

Public Function CompactDb(ByVal strDB As String, _
Optional strPW As String = "") As Boolean
Dim strNewDB As String, strLocale As String

On Error GoTo ErrorCDB
strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
Call Echo(True, "Compacting """ & strDB & """.")
If strPW <> "" Then strLocale = ";pwd=" & strPW
If Exist(strNewDB) Then Kill strNewDB
Call DBEngine.CompactDatabase(SrcName:=strDB, _
DstName:=strNewDB, _
DstLocale:=strLocale, _
SrcLocale:=strLocale)
Kill strDB
Name strNewDB As strDB
Call Echo(True, """" & strDB & """ compacted.")
CompactDb = True
Exit Function

ErrorCDB:
CompactDb = False
End Function


Zerin

[Killer42]

Personally, I think I would do the work in the VB program, but create a new table in the database to store the selected option(s).

The purge could be set up to happen when someone starts or leaves the program, or whatever you prefer.

As for NeoPa's suggestion, it may be a good one but I don't think it's relevant in this case. It is for "compacting" the database, which basially just reclaims the space taken up by records which have been deleted (wasted space). It has nothing to do with the actual deleting.

[MMcCarthy]

Dear mmccarthy ,

Thanks for your reply.Actually,I don't have any idea where to set the options. As far as I can understand,the delete method should find out the old data first and then delete them.Now, my software's requirement is that..............

1. The default value will be 1 month ( that means,identify first the old data and then delete every month's last day)

2. User choice should be given to select autopurging policy:

a) related to time ( Drop down list for selecting month)

b) related to disk space ( free 50% memory space or more)

The problem is,I can't understand where to add the modules...................in the database (Access 2003) or in the front end view (Visual Basic 2005 ) ? Actually,I don't know from where to start.

The database is working well.Only auto purging feature has to be added. Please help me.

Thanks in advance.

Zerin

Zerin if this was all being done in Access the function would go in a module and be called in an event on the form. I'm really sorry but I don't know how to do this in Visual Basic.

Mary

[MMcCarthy]

Zerin if this was all being done in Access the function would go in a module and be called in an event on the form. I'm really sorry but I don't know how to do this in Visual Basic.

Mary

Zerin

I'm going to ask someone else to take a look at this so don't give up yet.

Mary

[NeoPa]

Personally, I think I would do the work in the VB program, but create a new table in the database to store the selected option(s).

The purge could be set up to happen when someone starts or leaves the program, or whatever you prefer.

As for NeoPa's suggestion, it may be a good one but I don't think it's relevant in this case. It is for "compacting" the database, which basially just reclaims the space taken up by records which have been deleted (wasted space). It has nothing to do with the actual deleting.

What you say is absolutely true, except that I was replying to the original post and it's certainly not clear from that that record deletions are required rather than a Compact/Repair.
If they are of course, then the DELETE SQL posted by Mary should do. I suspect that the best idea is to put whatever code is used into the VB app rather than the database itself though, as you suggested.

sorry about the code btw.
The extra function is here in case it helps (Bear in mind what Killer says though, it may not suit your needs directly).

'Exist returns true if strFile exists.
'22/05/2003 Rewritten with better code.
'20/05/2005 Added finding of R/O, System & Hidden files
Public Function Exist(strFile As String, _
                      Optional intAttrib As Integer = &H7) As Boolean
    Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
End Function

[Zerin]

May GOD bless your holy souls !

This wish is for all who are trying to help me........................

For the auto purging data topic.....................

I think I should do it by Visual Basic................not in the database portion. I'm reading books and searching the web.In the meantime,you all also keep going.. ofcourse,if you have spare time for that.

I'll send my codes to this forum,if my project is done successfully.Cuase,it may be helpful for others.

Best regards,
Zerin

[sashi]

May GOD bless your holy souls !

This wish is for all who are trying to help me........................

For the auto purging data topic.....................

I think I should do it by Visual Basic................not in the database portion. I'm reading books and searching the web.In the meantime,you all also keep going.. ofcourse,if you have spare time for that.

I'll send my codes to this forum,if my project is done successfully.Cuase,it may be helpful for others.

Best regards,
Zerin

Hi Zerin,

Sorry for the late response, Mary had sent me a PM message to bring my attention to this thread. I think i delayed the process indirectly by not taking quick action.

Very well, since you have decided to look into VB as an option, bring the topic to VB forum, lets take a look to the best approach to solve the issue.

Take care & Have a great day ahead.