SFTP upload and downloand from Microsoft Access VBA Help Needed!

[CodeChica]

I have a database that outputs a file and I need to upload it via sFTP automatically, so I am using Access 2013 with the below code, which accesses an app called Putty to sftp. The code seems to be connecting to the server, but that's all that I can see that it's doing. It's not putting the file there. I think MS Access is not waiting and I need a ShellWait, but I am unsure of the code to use or how to, for Shellwait ...

Option Compare Database

'"C:\Program Files\PuTTY\pscp.exe" -sftp -l hans -pw changeme C:\Access\sample.txt 192.168.1.6:/home/hans/
'So this procedure will build and run that command string.

Public Sub SftpPut()
Const cstrSftp As String = """C:\Users\eay41o7\Downloads\putty\pscp.exe"""
Dim strCommand As String
Dim pUser As String
Dim pPass As String
Dim pHost As String
Dim pFile As String
Dim pRemotePath As String

pUser = "CareAdvantage"
pPass = "CareAdvantage01"
pHost = "mshrasftp.mckesson.com"
pFile = "O:\Corporate\FortWorth\SpecPharmacy\Report Repository CADV\Reports\Takeda_Shipping_Conf_Daily\Takeda_Data.txt"
pRemotePath = "/home/tomck/"

strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
" " & pFile & " " & pHost & ":" & pRemotePath
Debug.Print strCommand
Shell strCommand, 1 ' vbNormalFocus '


End Sub

 

[DBAMJA]

I'm seeing where you are telling putty to use sftp, the user id and password, the file to upload and where to upload it but I don't see anywhere in your code where you are telling putty to "PUT" the file. You need to use PUT and GET commands to send and receive files. Check the putty documentation on where in the command line you need to place the word PUT and that hopefully should fix the problem.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[CodeChica]

Ahhh...yes
The putty documentation does not load. It appears its no longer on the web. Can you give me the put line syntax and where to put it in my code, please?

 

[strongm]

>no longer on the web

Eh?

http://the.earth.li/~sgtatham/putty/latest/x86/putty.zip

contains all current release putty stuff, including help file

 

[DBAMJA]

I'm not really familiar with putty but just guessing here and you may need to play with it but my guess would be:

strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
" [highlight #FCE94F]PUT[/highlight]" & pFile & " " & pHost & ":" & pRemotePath

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[CodeChica]

I have the zip file containing on the documents, but when you execute the Help file within it, it does not load because it doesn't find the web address any longer.

 

[strongm]

What? It doesn't access a website.

Ah, wait one - are you using Windows Vista or later? In which case I suggest you try right-clicking on the putty.chm file (wherever you have extracted it to), and choose 'Properties' from the menu. Then on the general tab, click on Unblock.

 

[CodeChica]

Thanks! I actually googled it and found a good link.

 

[CodeChica]

Still no luck putting a file to the server ...I have verified that I am connecting and disconnecting, but I need to know where and how to include the PUT command in the strCommand of my code as every way I have tried it is either incorrect syntax or in the method below it accepts the code format but isn't putting a file to the server. Any help would be greatly appreciated.

Public Sub SftpPut()
Const cstrSftp As String = """C:\Users\eay41o7\Downloads\putty\pscp.exe"""
Dim strCommand As String
Dim pUser As String
Dim pPass As String
Dim pHost As String
Dim pFile As String
Dim pRemotePath As String


pUser = "CareAdvantage"
pPass = "CareAdvantage01"
pHost = "mshrasftp.mckesson.com"
pFile = "O:\Corporate\FortWorth\SpecPharmacy\Report Repository CADV\Reports\Takeda_Shipping_Conf_Daily\Takeda_Data.txt"
pRemotePath = "/home/tomck/"

strCommand = cstrSftp & " -scp -l " & pUser & " -pw " & pPass & _
" put " & pFile & " " & pHost & ":" & pRemotePath
Debug.Print strCommand
Shell strCommand, 1 ' vbNormalFocus '

End Sub