Link to home
Start Free TrialLog in
Avatar of Juan Ocasio
Juan OcasioFlag for United States of America

asked on

Accessing SFT server via Access 2010 using a batch or vbs file.

Hello all:

Hello all:
Currently I have the following set up: a directory with psftp.exe, my ppk file, and a batch file.  When I want to connect to the SFTP server, I open a cmd prompt, navigate to the directory and run the batch file.  It then connects me to the server.  I can then navigate to a folder and then upload a file running commands via the opened cmd prompt.

What I want to accomplish is doing all of this by calling a separate batch file (or something similar) from Access 2010.  So my thought is to run an external program (perhaps cmd prompt or powershell) using the Shell() function in Access, passing it the name of the file I want to put on the SFTP server.  The batch file (or whatever) would then do everything I normally do manually (open cmd, run batch file with credentials to log into the SFTP server, navigate to the directory on the server, and then put the file).

Is there a way to accomplish this through Access 2010?

Many thanks!

Juan
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Yes, the Shell function is for such purposes.
Avatar of Juan Ocasio

ASKER

Thanks Gustav:

I'm trying to create the script that would perform the tasks outlined in my question.  So I'm looking for a way to, from Access 2010, use my existing bat file with my credentials to connect to an sftp server and then send a file that I supply as an argument to the shell function.

Thanks for your input.

Juan
I located this page which might help you with the script:

https://the.earth.li/~sgtatham/putty/0.52/htmldoc/Chapter6.html
I found that as well and sadly it didn't help much.  Hopefully someone has done something similar (working with an ftp server outside of access) to use with access.

Thanks!

Juan
Really? Paragraph 6.1.5 contains all info for a working example.
While it has information on specific commands, I need to know how to set up the batch file so that it calls my batch files with my private key (credentials), and once connected cds to the destination folder and put the file.  I can do all of that via a cmd prompt, I'm trying to figure out how to put all of that in a batch file that access will call, passing in the location and file name of the file I am trying to upload.

So as an example I want to create SendFile.bat that uses my credentials.bat file to connect to the sftp server.  So from Access, the Shell command would look like this:
Shell("SendFile.bat " & strFileLocation)  <- strFileLocation is the path to the file that I am sending



Once connected, SendFile.bat will cd to the folder (as an example /Employees) and then upload the file denoted by the strFileLocation variable.

I'm looking for the code that would go into SendFile.bat.

The other thing that section 6.1.5 is assuming is that you're already connected to the sftp server, which from Access I will not be.

Thanks again,

Juan
You can use a parameter token for the batch file to pick up the filename.
I located this among many guides how to use that feature:

Command line parameters

like you wrote:

Shell("SendFile.bat " & strFileLocation & "") 

Open in new window

Note, that will probably need the full path to the batch file.

The batch file - as the name implies - simply holds a list of your commands.
If you have trouble assembling this, we must know the command lines to execute.
Thanks Gustov:

Perhaps I've been a bit ambiguous.  I'm looking for a solution that would show me how to implement what I am seeking.  I want to use Access 2010 to call a batch file.  Inside the batch file it will call my credentials.bat file.  This file connects to the sftp server using psftp.  Once connected, the batch file will navigate to a specific folder (my example had /employees) and then put a file that was supplied as an argument.  Not sure what I'm missing or what is unclear about my problem.  I can open a cmd prompt, and use ./credentials.bat cmd and it run my script to connect to the sftp server.  I can then cd to Employees, and then manually put a file.  I need to know how to construct the bat file that is being called by access 2010 to do this automatically.

thanks again,

Juan
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'll give this a try.  I didn't want to modify my credentials.bat because it contains my the entire script to access the ftp site.  We're using a private key instead of a password.
 Looking at your suggesting, it may work if I add all of the data together.  I'll let you know how it goes.

thanks!
Well, as your current batch file only does a part of the task, you will have to modify it.
Thanks:  I know there is a way to do it separately, I just don’t know what the syntax is. My only problem with the solution you proposed is multiple people may be pushing files at the same time. If they’re creating a new file on the fly, there is potential for conflicts and overwrites. I’ll dig in a little more to see if I can use your suggestion as a possible solution. Thanks again,

Juan
Then include the user initials or similar in the script filename, or use the user's %localappdata% folder to hold a subfolder for the script.
I'll check to see what I can do.  I was hoping for a turnkey solution, but I realize I can't always get that :)  Access 2010 is on a server, and the file location is on a different server, so they won't have a appdata folder on the file server.  I'm going to continue to try using the method I was thinking about (a batch file that calls my batch file), but will see if I can incorporate pieces of your solution.

Thanks again.

Any other suggestions, or full blown solution on my requirements are more than welcome from the community :)

Juan
Thanks Gustav:

While not exactly what I was looking for, it at least pointed me in a different direction I can try.

Thanks again!

Juan
You are welcome!