By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Auto Purging data

Zerin
P: 64
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
Dec 13 '06 #1
Share this Question
Share on Google+
13 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 13 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
If you are trying to purge unused data from your database this function should work for you.
Expand|Select|Wrap|Line Numbers
  1. 'CompactDb compacts a remote Access database.
  2. Public Function CompactDb(ByVal strDB As String, _
  3.                           Optional strPW As String = "") As Boolean
  4.     Dim strNewDB As String, strLocale As String
  5.  
  6.     On Error GoTo ErrorCDB
  7.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  8.     Call Echo(True, "Compacting """ & strDB & """.")
  9.     If strPW <> "" Then strLocale = ";pwd=" & strPW
  10.     If Exist(strNewDB) Then Kill strNewDB
  11.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  12.                                   DstName:=strNewDB, _
  13.                                   DstLocale:=strLocale, _
  14.                                   SrcLocale:=strLocale)
  15.     Kill strDB
  16.     Name strNewDB As strDB
  17.     Call Echo(True, """" & strDB & """ compacted.")
  18.     CompactDb = True
  19.     Exit Function
  20.  
  21. ErrorCDB:
  22.     CompactDb = False
  23. End Function
If not, then please explain your question more clearly so we can help you.
Dec 14 '06 #3

Zerin
P: 64
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
Dec 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.    strSQL = "DELETE * FROM TableName;"
  4.    DoCmd.RunSQL
  5.  
  6.  
should do the job.

Mary
Dec 20 '06 #5

Zerin
P: 64
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
Dec 21 '06 #6

Zerin
P: 64
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
Dec 21 '06 #7

Zerin
P: 64
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
Dec 21 '06 #8

Expert 5K+
P: 8,435
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.
Dec 22 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 22 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 22 '06 #11

NeoPa
Expert Mod 15k+
P: 31,186
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).
Expand|Select|Wrap|Line Numbers
  1. 'Exist returns true if strFile exists.
  2. '22/05/2003 Rewritten with better code.
  3. '20/05/2005 Added finding of R/O, System & Hidden files
  4. Public Function Exist(strFile As String, _
  5.                       Optional intAttrib As Integer = &H7) As Boolean
  6.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  7. End Function
Dec 22 '06 #12

Zerin
P: 64
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
Dec 28 '06 #13

sashi
Expert 100+
P: 1,754
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.
Dec 28 '06 #14

Post your reply

Sign in to post your reply or Sign up for a free account.