Move Files

[prasadgov]

Hi,

I download files from a FTP server using WinSCP and it works well. The WinSCP code is triggered via Shell command from a Access module. The files are placed in the folder where the WinSCP exe is and then moved to another folder. The Move code works well during the first instance. But when i run the WinSCP command again and after the files are downloaded, the Move code fails. It comes with a Permission denied error. I am not sure why.
I need to run the WinSCP code every hour but since the Move fails after the first instance, I am unable to use the Task or Windows Scheduler.

TIA

 

[Pat Hartman]

How are you running this? Are you using Windows to schedule a MS Access application and Access is using shell to run the download? Are you sure the application is being properly closed when the code finishes? If not, you will have trouble if the app remains open. This is also pretty convoluted. Why not just use a script to run WinSCP and not involve Access at all?

 

[prasadgov]

How are you running this? Are you using Windows to schedule a MS Access application and Access is using shell to run the download? Are you sure the application is being properly closed when the code finishes? If not, you will have trouble if the app remains open. This is also pretty convoluted. Why not just use a script to run WinSCP and not involve Access at all?

I have created a batch job to be placed on the scheduler, which opens the DB and runs the macro, mcrFullrun. Currently, I am testing the code manually.

While I have the DB open, I am running the macro That I created for the Full run. This macro, mcrFullrun has all the modules, queries, including the WinSCP downloads, Move Files among others. It runs well without issues during the first instance. When I try immediately after few minutes, the downloads work but it fails during the Move.
Do you mean, I need to exit the DB and try again to see if it works?

 

[Pat Hartman]

Do you mean, I need to exit the DB and try again to see if it works?

The way you are running this - YES - you must close the database between executions.

 

[The_Doc_Man]

I'm going to hazard a guess that you are trying to move a file that is still open. File system locks would prevent that. Do you use a QUIT command (or whatever is appropriate) to shut down WinSCP before you attempt the move? If Access itself doesn't open any of the files, then the problem with moving doesn't come from Access.

 

[Pat Hartman]

Doc, I think there is a script that runs periodically. It opens the db to a macro which does something. However, if the macro doesn't end with a command to shutdown the database, the database remains optn. Then when the timer ticks down, the script runs and tries to open the already open database.

 

[The_Doc_Man]

Ah, that would certainly cause a problem. If this is one of those scripts that launches a Macro via (I think) the /X:macroname options, and if that macro includes opening some files FROM ACCESS, they would not be closed/released again until Access closes. If Access never actually closes because the Macro doesn't end in Application.Quit, ... there you go.

 

[prasadgov]

Doc, I think there is a script that runs periodically. It opens the db to a macro which does something. However, if the macro doesn't end with a command to shutdown the database, the database remains optn. Then when the timer ticks down, the script runs and tries to open the already open database.

The last command in the macro is Application Quit

 

[Isaac]

I'm pretty sure your problem is in the WinScp command. Something is not releasing those files and it has nothing to do with Access, as you noted you're unable to use task scheduler either. Something may be continuing to run WinScp instead of terminating the process or the application.

I have had GREAT success in posting questions for the WinScp developer, who generously contacted me to answer my question.

Try it https://winscp.net/forum/viewtopic.php?t=31265

PS for fun, I tried to find out if Martin was KingMartin from UA, as there were similarities, but he never told me either way LOL

 

[prasadgov]

I'm pretty sure your problem is in the WinScp command. Something is not releasing those files and it has nothing to do with Access, as you noted you're unable to use task scheduler either. Something may be continuing to run WinScp instead of terminating the process or the application.

I have had GREAT success in posting questions for the WinScp developer, who generously contacted me to answer my question.

Try it https://winscp.net/forum/viewtopic.php?t=31265

PS for fun, I tried to find out if Martin was KingMartin from UA, as there were similarities, but he never told me either way LOL

I do have Exit command at the end of my WinSCP script. I have the Download from WinSCP followed by the MoveFiles code. Is there a way to pause or delay the MoveFiles code by few seconds and trigger it after?
Probably a Sleep or Wait code between WinScp and Move?

 

[Isaac]

Sub MakeCodeWait(strSeconds as string)
dim dtNow as Date
dtNow = now()
Do until now()>dtnow + timevalue("00:00:0" & strSeconds)
   do events
Loop
End Sub

MakeCodeWait "5"

....note, that's untested memory code from my memory of a function I used to use a lot

You can get fancier and execute a loop every 2 seconds of code that tests if the file is read only or not, but hopefully this gets you where you need to be well enough?

 

[Gasman]

I can just see that being copied as is

do events is one word @prasadgov

 

[Isaac]

I can just see that being copied as is

do events is one word @prasadgov

thank you

 

[Gasman]

thank you

Only know, as I copied it myself to try out.

Thank you @Isaac