Delete file based on query.

[Crilen007]

Ok, I have kind of a confusing thing.

Every employee has an ID, and a start date.

Some employees have pictures (stored in a folder).

When someone is terminated, the picture does not get erased (as it shoud).

So, I need to run a query that deletes any pictures older than the start date of the employee.

Any help on this? Not sure where to begin. I suppose I should create a query with employees who have pictures. But how do I compare a files date to an entry in a table?



Example table:

ID      Emp            Start         File
101    John Smith   5/1/2005    \\file_server\emppic\101.jpg

 

[ghudson]

I do not have the time to work out the details for you but here is a suggestion.

You need to get a listing of all your file names. You need to compare that listing of file names against the users ID number. Delete all files that do not have a matching ID number. The Kill() command is what you will use to delete the files. There are a lot of threads that can also get you started with the "FileSystemObject" which is a powerful method that you could use to grab a recordset of the file names and delete the files that do not have a matching user ID.

Check out my Browsing sample Browse [Find a directory or file] to see how I populate a table with a listing of all files in a specific directory. You can strip off the .jpg extensions or your can use the Right() function to grab the file name after the .jpg for there are a lot of ways to do it.

There are a lot of different approaches you can take to your quest.

 

[hooks]

I have an application that does something similiar. How i handle this is i delete the contents of the emppic folder then import all active employees. We are doing it a little different i guess.

Are you using bound or unbound forms and recordsets??

Here is the code that will delete the Employee Picture

[COLOR=Green]Create your database Connection[/COLOR]

Dim Sql as string
Dim rs as new ADODB.Recordset
Dim Conn as new ADODB.Connection

Sql = "Select ID, Emp, Start, File from TableName Where ID = [Employee to delete]"
 
rs.Open Sql, Conn, adOpenKeyset, adLockOptimistic
 
[COLOR=Green]
''**   If Employee that is being deleted has a picture file then delete it.[/COLOR]
 If Dir("\\file_server\emppic\" & rs![File]) <> "" Then
    Kill Dir("\\file_server\emppic\" & rs![File])       
 End If

[COLOR=Green] After deleting Employees picture, delete the employee from the database[/COLOR]

This is just a very rough example of deleting a file.

Hope this helps a little.

 

[Crilen007]

I have to compare the start date of the employee to the creation date of the file. If the employee started work after his picture was created, the picture gets erased.

If it was the User ID, it would be easy hehe.

I am trying to figure out how to pull the file creation date from a file.

I tried to find some examples but they were VB only examples. I wasn't really sure how to use them in access.

 

[ghudson]

The VBA FileDateTime() function will give you the date the file was last modified. Search the Access help files or the forum for more info or examples.

 

[Crilen007]

You rule ghudson!


Thanks, I will look into that command.