File moving system - best method? (1 Viewer)

File staging/copying/moving system - better method?

Hello all!

It's been quite some time since I've been able to post here but I am in a bit of a quandry here redesigning a system I created a few years ago and hope someone can lend some help with a possible better solution for my code. I haven't been able to find anything here that really fits my scenario so I started a new thread. If this already exists please help point me to where it is.


Ok, so I have a system that monitors a folder for incoming files. When files arrive the file name is interrogated based on a "standard" structure for certain parameters specified in a table and an attribute text file is produced based on those values. The creation time of the file and size is also recorded.

The original file is then copied to a "retain" folder on the main server, the original file is then moved to a server location based off of the stored values in a reference table and the text file is placed with it for another system to pick it up.

To avoid "file not found" errors due to a transfer not being complete I set a "release time" based on the file size and time of appearance so for instance a 12gb file @ 15:00 is held until 15:10 to allow for full transfer before attempting to copy or move it.

The other issue I have is when the files are transferring the interface is "not responding" so nothing can be done until the files have finished processing. I've tried using DoEvents to return focus to the interface while the process runs in the background but this hasn't really worked too well. The interface shows things like what files are in the queue, what has moved, and allows for parameter changes (which obviously should not be changed while files are moving) and information lookup.









So I guess based on the above information my questions are:

1. Is there a better method than "timers" for triggering my copy/move process?
2. Is there a more efficient way to handle the processing of the files (copy/move) and allow the interface to still be able to work while the files transfer in the background?

This is what the current system uses but needs to be updated for "v2.0" unless there is no better way (which there has to be) as I am fairly novice in this area

-----------------------------------------
Dim attdb As DAO.Recordset
Set attdb = CurrentDb.OpenRecordset("ATT_qselReleased", dbOpenDynaset)
If attdb.BOF Or attdb.EOF Then
attdb.Close
Set attdb = Nothing
Exit Function
Else
Dim Prod As String
Prod = attdb!path & "\"
End If

Dim rsn As DAO.Recordset
Set rsn = CurrentDb.OpenRecordset("ATT_tblATTtemp", dbOpenDynaset)
If rsn.BOF Or rsn.EOF Then
Exit Function
Else
rsn.MoveFirst
End If
Dim FLN As String
FLN = rsn!FileName
'Create .att file in Prod folder
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("ATT_qselFileInfo", dbOpenDynaset)
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile(Prod & FLN & ".att", True)
TextFile.WriteLine (rst!a)
TextFile.WriteLine (rst!b)
TextFile.WriteLine (rst!d)
TextFile.WriteLine (rst!e)
TextFile.WriteLine (rst!f)
TextFile.WriteLine (rst!g)
TextFile.WriteLine (rst!h)
TextFile.WriteLine (rst!i)
TextFile.Close
'End .att file
'Copy and move production jobs
FileCopy PTN & FLN, Prod & FLN
Name PTN & FLN As ret & FLN

attdb.Edit
attdb!Proc = 1
attdb.Update
Dim rsl As DAO.Recordset
Set rsl = CurrentDb.OpenRecordset("ATT_tblFileLOG", dbOpenDynaset)
'Add logfile
rsl.AddNew
rsl!FileName = FLN & ".att"
rsl.Update

Dim fsz As Integer
fsz = rsn!Size

rsl.AddNew
rsl!FileName = FLN
rsl!Size = fsz
rsl.Update

rsl.Close
Set rsl = Nothing
rsn.Close
Set rsn = Nothing
Loop
rsp.Close
Set rsp = Nothing
DoCmd.OpenQuery "ATT_qdelATTtemp"
Exit Function
---------------------------------------

Hopefully this makes sense

Q: Is there a better way than timers to trigger your file sweeps?
A: In this case I don't think so. Since the job is recurring you are reliant on something happening again and again, a timer is pretty much what you need.
Q: Is there a more efficient way to handle file copies that allows the interface to still be interactive.
A: More efficient...not that I am aware of, allows the interface to be interactive. Here you have a couple options, you could change the copy method to use the Shell Copy API example here http://www.freevbcode.com/ShowCode.asp?ID=499 or you could use the Open command to open the files and then copy the contents over 1 or more bytes at a time.

Note: The shell Copy does not make your interface interactive...but it pops up the progress box the same way as if you copied a file using the file explorer.

Thanks for the reply DJkarl...

Since this system is largely passive I won't be able to use a shell command that requires user response. I actually use an unhandled error of "file already exists" to stop all the code until the issue is resolved. This in turn also alerts another system that watches the log files of the problem which then instructs the operator(s) to call me (often waking me up in the middle of the night) to fix the issue.

If nothing else I guess I feel a little better that it seems I basically had the right idea for my situation the first time I did this. Just bummed it doesn't seem I'll be able to improve on it much

Thanks again.