Exporting records based on date

[justinwright]

I wanted to be able to export records where DATEPROCESSED is older than either:



90 days
180 days

This would optimistically be accomplished by a drop down menu (cboDateBackup) and a button (cmdBackup). I have the code to copy the DB here (with a backup datestamp):

Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "S:\Folderwhereitis"
    ' Path for the original database
newPath = "S:\Folderwhereitis\BACKUP"
    ' Path for backup of database
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "\" & "thingy_be.accdb", newPath & "\" & "thingy_be_" & Format(Date, "mm-dd-yyyy") & ".accdb"
' Copying the database
Set fs = Nothing

My question is how would I structure the VBA command to select only the desired files (then subsequently delete them from the current table)? The table name is TrackingTable.

Any help is much appreciated!

[HiTechCoach]

I wanted to be able to export records where DATEPROCESSED is older than either:

90 days
180 days

This would optimistically be accomplished by a drop down menu (cboDateBackup) and a button (cmdBackup). I have the code to copy the DB here (with a backup datestamp):

Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "S:\Folderwhereitis"
    ' Path for the original database
newPath = "S:\Folderwhereitis\BACKUP"
    ' Path for backup of database
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "\" & "thingy_be.accdb", newPath & "\" & "thingy_be_" & Format(Date, "mm-dd-yyyy") & ".accdb"
' Copying the database
Set fs = Nothing

My question is how would I structure the VBA command to select only the desired files (then subsequently delete them from the current table)? The table name is TrackingTable.

Any help is much appreciated!

What are the fields in the table TrackingTable?

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

NUMBERONE - number, long int
AMOUNT - number, double, currency
PURCHDATE - date/time
DATEENTRY - date/time
PENDDATE - date/time
WAS - text
INFO - text
TYPE - text
FEEDUE - number, double, currency
FEEPAID - number, double, currency
NAME1 - text
ADDRESS - text
CITY - text
STATE - text
ZIP - text
PHONE - text
FAX - text
EMAIL- text
PROOFPURCH - text
TYPEPROOF - text
EXPPROOF - text
PROCESSED - text
DATEPROCESSED - date/time
INITIALENTRY - text
INITIALPROCESS - text
COMMENTS - text
OUTSTANDINGFEE - number, double, currency
ATTACHMENT - attachment
LATESTFEEPAID - number, double, currency
FEEPAIDDATE - number, double, currency

[HiTechCoach]

The SQL to select the records that are 90+ days old would be:

Select TrackingTable.* From TrackingTable Where TrackingTable.DATEPROCESSED  < (Date() - 90)

or

Select TrackingTable.* From TrackingTable Where TrackingTable.DATEPROCESSED  < (DateAdd("d", 90, Date())

To delete the same records use:

Delete TrackingTable.* From TrackingTable Where TrackingTable.DATEPROCESSED  < (Date() - 90)

I normally NEVER delete records like this. I use a "deleted" flag or "archived" flag. I then filter out the records marked are deleted or archived from all the queries/forms/reports, etc.

At some scheduled DB maintenance time:

1) Take the database off line.
1) Mmake a backup
2) Export all the record marked as archived/deleted
3) Compact and the database. This is important after mass updates.

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

The main reason I want to remove old items is because of the DB limit on Access. At the moment I'm using the attachment field (I'm open to using the external method if anyone has any links to it), and the files are PDF. So, when it reaches a certain limit it will stop, right (like 2 GB I think?)? That's why I came up with the idea of exporting old, archived files and deleting them to clear out space.

[HiTechCoach]

The main reason I want to remove old items is because of the DB limit on Access. At the moment I'm using the attachment field (I'm open to using the external method if anyone has any links to it), and the files are PDF. So, when it reaches a certain limit it will stop, right (like 2 GB I think?)? That's why I came up with the idea of exporting old, archived files and deleting them to clear out space.

I see the issue. Database bloat. You are correct that an Access database has a 2 gig file size limit.

If you want to store files in the database then I would upsize the back end to an SQL server.

I have work several commercial document management systems that used an SQL server back end. I have not seen one yet that stored the file within the database.

I made a little example to help show how I woudl recommend handling this:

Document Links 2

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

I hate to deviate too much, but that looks very similar to something I've been looking for for a while.

The idea (optimistically, though I would appreciate any informative links like that one to help) would be to store them in an external place, we'll say S:\ProjectDirectory\AttachmentsHere\ (PDF files with like the same value as NUMBERONE concatenated with _ and 1 - 3 ,for attachment files 1 through 3) then have a form to look up the files, display them, and be able to add/delete them. The sample you gave me gave me some really good ideas, but I'm thinking with this external linking it will prevent me from having to use the mass delete from the DB (since the attachment files are the only thing that will make the DB too large, and have me able to display to the user said files with the capability to open/remove/add (separate forms for addition/removal/opening is the goal). I posted something similiar, but I didn't really convey very well what I was asking. Normally I wouldn't deviate so far off topic, but you seem to be able to help me, and frankly I've looked around so much for weeks that I'm about to rip my hair out, the thread is here:

https://www.accessforums.net/forms/p...-box-8162.html

Obviously you don't have to feel obligated, but any help is much appreciated since at this point I'm at my wits' end. If I have to, I'll do the whole integrated attachments thing in the DB, but I was rather fond of the idea of using an external reference from the beginning

[HiTechCoach]

If I have to, I'll do the whole integrated attachments thing in the DB, ...

From what I have read everyone is saying tyhat they do NOT use the attachment data type. I don't know of any expereuinced Access developers that use it. I have not seen anyone so far recommend that you use the "whole integrated attachments thing."

I took a look at the other threads. It appears that you are trying to use an unbound form to display data from a recordset. One of the fields in the recordset being a multi-value attachment data type. Is this correct?

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

That is correct. The file type will be PDF, I'd like up to three attachments, and I'd like a way to view (populating, like that topic says), and perhaps a way (maybe on a separate form?) to remove or add a particular attachment.

I hate having to ask so much, but I swear I've looked absolutely everywhere, lol, and its either so confusing that I have no idea what's going on or it's not related to the problem in question at all, which is odd since I'd really bet that people have to work with attachments all the time.

[HiTechCoach]

That is correct. The file type will be PDF, I'd like up to three attachments, and I'd like a way to view (populating, like that topic says), and perhaps a way (maybe on a separate form?) to remove or add a particular attachment.

If you are using an Unbound form to show an multi-value attachment data type then that is part of the problem. You need to use a bound form.

IMHO, Access's bound forms is one of the best features. From looking at your post it appears you are do9ing a lot of unbound forms. Is this correct?

Your search form probably can be done with form and bound sub form. This would eliminate the need for all the recordset coding.


Are you required to use the attachment data type?

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

Well, some type of way to add and easily access (or remove) said file is a huge part of the program. I have the form displaying all the other stuff, so I suppose it wouldnt be too much trouble to add a "view attachments" button to the main screen (separate from the lookup form?).

I have two unbound forms, a lookup form and a report generation form (the reports themselves are bound, though).

And I'm not sure on how to implement it using the bound form and subform, I'm more of a VB coder than someone that's more accustomed to using the Access UI (irony, I know) to accomplish very much.

[HiTechCoach]

Well, some type of way to add and easily access (or remove) said file is a huge part of the program. I have the form displaying all the other stuff, so I suppose it wouldnt be too much trouble to add a "view attachments" button to the main screen (separate from the lookup form?).

I have two unbound forms, a lookup form and a report generation form (the reports themselves are bound, though).

And I'm not sure on how to implement it using the bound form and subform, I'm more of a VB coder than someone that's more accustomed to using the Access UI (irony, I know) to accomplish very much.

That explain a lot.

AFAIK, you need to use a bound form when working with a multi-value attachment field. It works very similar to a sub-table.

I have tried using the attachment field in my document management systems but have since condoned then because of so many issues. As you are fining, not many Access gurus have used them either. My guess is that they had issues with them like I did.

I get the feeling that you are set on using the attachment data type. Check out this tutorial from a friend who wrote a book about 2007:

Microsoft® Access 2007 Working with the Attachment DataType

Hope this helps.

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

The irony is that I have two parts of that tutorial printed out

Well the actual external attachment method (not using the field in the DB) might actually be a better option. The only problem is is I'm not sure how to use that, so if you had a link on that that might be the better option. Again, sorry I'm rather helpless at this :\

[HiTechCoach]

The irony is that I have two parts of that tutorial printed out

Well the actual external attachment method (not using the field in the DB) might actually be a better option. The only problem is is I'm not sure how to use that, so if you had a link on that that might be the better option. Again, sorry I'm rather helpless at this :\

For a link see Post#6 above. I use a form like this as a sub form for a sub/child related table. This will basically give you the similar functionality as the multi-value attachment using an external link.

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[justinwright]

I can see where it opens files, and stores a location for the files already there (they seem independently loaded, not through the program). How would you remove a file? When said file is removed, I'd like it removed from the actual system if possible, as well as all the links to it (the Stored Path in your example) removed as well.

How would you add it? Say, if you had a file on the desktop, to copy it and rename it to the appropriate thing (ie the concatenation mentioned earlier of the NUMBERONE field + _ + number one through three depending if one or two exist for easy referencing) all through a form/browse control.

I like the idea, and its basically exactly what I'm looking for, if there is some way to delete the file and to add one (initially through the input screen - as you're entering the record for the first time - or an "add info" screen - adding to a current record, looked up by NUMBERONE - as well) and the record be kept.