How to use sftp from within an MS Access database module

How to use sftp from within an MS Access database module

Posted by: 08-bases-mohair on 20 December 2022, 11:06 pm EST

• 

  08-bases-mohair

  • Post Options:
  • Link
    Copy

  Posted 20 December 2022, 11:06 pm EST

  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?

• 

  jimmy.wick90

  • Post Options:
  • Link
    Copy

  Posted 10 April 2023, 8:43 pm EST

  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.