How to use sftp from within an MS Access database module?

Question

I have a requirement to create a simple database in Access to collect some user data that will be loaded into another database for further reporting. There will be a module in the Access db that when invoked by the user (probably by clicking a button) will output a query to a delimited file. The user also needs a mechanism (for example a form with a button) to easily transfer the file to a remote server, using sftp. Does anyone have an idea of how to accomplish this?

Answer 1

I would imagine you just need to find an FTP COM object. You should then be able to instantiate this in the Access module code. dart.com has one and I'm sure there are many more (just Google FTP COM).

Answer 2

You can simply write a call to the sftp command line client via a batch file if you want to accomplish that.

Check out the Shell() function in VBA.

Under the click event of the button on your form add in the code:

mySFTPCall = "sftp <insert your options here!>"
Call Shell(mySFTPCall, 1)

I've used this before to just copy files straight across network shares etc. to share data from an in-house Access DB. Of course you could get more fancy if necessary.

Answer 3

You could make a shell call to a command line app such as MOVEit Freely, which is freeware, to script secure ftp transfers. I've used it in the past and it has some nice features and worked quite well.

MOVEit Freely is a free command line FTP/secure FTP SSL (FTPS) client for Windows Vista Business Edition, 2003, XP, 2000, ME and NT 4.0 systems.

Answer 4

Chilkat has an FTP component that works with COM/ActiveX. It says that it supports FTP over SSL (FTPS), which I think is the same as SFTP. I've used some of their other products before for .Net and they have worked very well. They have a free trial, so you having nothing to lose by downloading and checking if they work for you.

Answer 5

You can use our SFTPBlackbox (ActiveX Edition).