Using FTP

[David44Coder]

I'd like to sent and get an access db to an FTP site to two of us can use the same file.
I've found a few things with Google (e.g. https://www.codeproject.com/Questions/321083/Automatic-FTP-via-Excel-VBA-Macro)
but can't get anything working. That is, even just to connect.
Doe anyone have something that works, or anything else I could try?
Thanking you.

 

[Gasman]

FTP does not allow sharing a file.
The name gives that away. It is for transferring files.

 

[The_Doc_Man]

You can transfer a copy of a closed file via FTP and open the copy at the remote site if that remote user also has Access. FTP is a whole-file transfer protocol. HTTP (the popular web protocol) and its variants also do not work any better when trying to share Access files. HTTP can transfer a file but again it is a whole-file transfer and is not well suited for Access sharing.

Access uses Server Message Block (SMB) protocol, which is the default Windows File Sharing and Printer Sharing protocol. It is TOTALLY different than FTP or HTTP. It allows you to interact with specified parts of a file. It is this latter protocol that makes it possible to share Access among local users on a local area network (LAN).

The problem with Access sharing is that once you leave the relatively stability of a LAN, you lose reliability of connections. And it is unfortunately true that when you have a less reliable network connection, you have a huge tendency to corrupt a remotely connected database. Once the remote DB gets badly corrupted, it gets useless.

You can use the forum's search capability to see articles regarding Access and web features. The short answer is "extremely limited."

 

[David44Coder]

Sorry, by sharing I didn't mean simultaneously. Just both use /access the file. We are using Filezilla to Get & Put it at the moment but if there' a way to automate that it'd be much better. Once transferred to local drive another instance of Access read/.writes to its table.

 

[The_Doc_Man]

I have seen successful scripts that could use FTP for automated file exchange, but don't have any available at the moment for examples. The question of "automated" becomes a more complex issue since the Access app you would want to use for the DB cannot easily also be the one that does the transfers. Between file locking and login issues, there might be a few barriers to success. Not saying impossible - but might be tricky to get it right. For instance, how will you know when it is safe to do an FTP "GET" operation to get your updated back-end file? If the file is open, you cannot guarantee that at any particular time you would be able to do the transfer 100% completely. I.e. while the DB you want to use is still open, you have the issue similar to taking a snapshot of energetic children with slow film cameras. Any blurring of the image and you have corruption.

 

[Gasman]

Sorry, by sharing I didn't mean simultaneously. Just both use /access the file. We are using Filezilla to Get & Put it at the moment but if there' a way to automate that it'd be much better. Once transferred to local drive another instance of Access read/.writes to its table.

You could create a command or batch script that would transfer the file to your computer and then open access to that file.
When you close Access run another which puts it back.

Using Windows FTP Scripts To Automate File Transfers | JSCAPE

Automate file transfers with Windows FTP scripts. Execute commands through batch files or PowerShell to simplify workflows, save time, and minimize errors. Consider secure alternatives like FTPS or SFTP for production environments.

www.jscape.com

Some people do much the same thing on getting the file on a network to the local PC. They do not put it back though.

 

[MsAccessNL]

I made a testdb with syncing edited data over onedrive. Every record update is written to a small text file. The idea is to work with seperate databases and only transfer the changed data. I found a need trick to overcome the ID number problem when entering a new record. The nice thing about this system is that you can work offline to, even change data. Would be great for countries with bad internet connections. Using one sharepoint table or one (only for the changes) table from you website server is also an option.

 

[The_Doc_Man]

@MsAccessNL

That is basically a transaction-oriented method, which worked well for the U.S. Navy (and other military services) for more years than I can count. It goes back to the days of magnetic tape as a transfer medium. In fact, even after the advent of networks robust enough to support an FTP-style interchange, the process was still referred to as a "tape cut" in the operational manuals... at least for a while.

The idea is you don't share the whole DB but rather create transaction files in a format needed by the partner offices. Works great as long as you think about when you want to send transactions and what goes into them. There is also the issue that transfer-file naming might be important since Windows will enforce a name alteration such as "(2)" if you attempt to create a file with the same name. We usually made a short name and then suffixed it with a date/time string of some sort. That way, if order was important, we knew the order in which the transactions were created.

 

[MsAccessNL]

@MsAccessNL

That is basically a transaction-oriented method, which worked well for the U.S. Navy (and other military services) for more years than I can count. It goes back to the days of magnetic tape as a transfer medium. In fact, even after the advent of networks robust enough to support an FTP-style interchange, the process was still referred to as a "tape cut" in the operational manuals... at least for a while.

The idea is you don't share the whole DB but rather create transaction files in a format needed by the partner offices. Works great as long as you think about when you want to send transactions and what goes into them. There is also the issue that transfer-file naming might be important since Windows will enforce a name alteration such as "(2)" if you attempt to create a file with the same name. We usually made a short name and then suffixed it with a date/time string of some sort. That way, if order was important, we knew the order in which the transactions were created.

That’s correct. Short name + date/time suffix. The file with latest date get imported first. I think it would also be a great sollution for big databases which are becoming slow over the internet. Having the main data stored locally and only transport the transaction. I started to make the test db with text files so i could replace them with rest api calls, which makes it possible to connect to any online database (even the free ones, you hardly need any storage capacity). Only one table is need to communicate the transactions. The rest api calls could also be made async though the webbrowser control. Rest api calls would also solve the id problem when entering a new record.

 

[The_Doc_Man]

For us, a secondary but important issue was that since we sent a transaction file, we could develop a digital signature of that file to verify that we got it intact using our automatic transfer software.

 

[Pat Hartman]

I made a testdb with syncing edited data over onedrive. Every record update is written to a small text file. The idea is to work with seperate databases and only transfer the changed data. I found a need trick to overcome the ID number problem when entering a new record. The nice thing about this system is that you can work offline to, even change data. Would be great for countries with bad internet connections. Using one sharepoint table or one (only for the changes) table from you website server is also an option.

This will not work when multiple people are able to update the same database at the same time.

When using Filezilla (and probably other FTP products) you can map the drive. This makes it operate just like any other mapped drive so no special code is needed. You just use standard FSO commands to manipulate the files.

 

[MsAccessNL]

@MsAccessNL

That is basically a transaction-oriented method, which worked well for the U.S. Navy (and other military services) for more years than I can count. It goes back to the days of magnetic tape as a transfer medium. In fact, even after the advent of networks robust enough to support an FTP-style interchange, the process was still referred to as a "tape cut" in the operational manuals... at least for a while.

The idea is you don't share the whole DB but rather create transaction files in a format needed by the partner offices. Works great as long as you think about when you want to send transactions and what goes into them. There is also the issue that transfer-file naming might be important since Windows will enforce a name alteration such as "(2)" if you attempt to create a file with the same name. We usually made a short name and then suffixed it with a date/time string of some sort. That way, if order was important, we knew the order in which the transactions

@MsAccessNL

That is basically a transaction-oriented method, which worked well for the U.S. Navy (and other military services) for more years than I can count. It goes back to the days of magnetic tape as a transfer medium. In fact, even after the advent of networks robust enough to support an FTP-style interchange, the process was still referred to as a "tape cut" in the operational manuals... at least for a while.

The idea is you don't share the whole DB but rather create transaction files in a format needed by the partner offices. Works great as long as you think about when you want to send transactions goes into them. There is also the issue that transfer-file naming might be important since Windows will enforce a name alteration such as "(2)" if you attempt to create a file with the same name. We usually made a short name and then suffixed it with a date/time string of some sort. That way, if order was important, we knew the order in which the transactions were created.

How did you solve the new ID problem when entering a new record?

 

[MsAccessNL]

This will not work when multiple people are able to update the same database at the same time.

When using Filezilla (and probably other FTP products) you can map the drive. This makes it operate just like any other mapped drive so no special code is needed. You just use standard FSO commands to manipulate the files.

Every user only updates his/het local databse. One drive is only holds small textfiles with edited data.

so you can map one drive with ftp?

 

[Pat Hartman]

Every user only updates his/het local databse.

Then what is the point of using OneDrive? Why is the database not on the local drive?

OneDrive is not FTP but you can map a drive to your OneDrive. So, technically OneDrive is a specialized type of FTP since they serve the same purpose which is viewing data on a remote drive or transferring it between the remote drive and a local drive.

 

[The_Doc_Man]

How did you solve the new ID problem when entering a new record?

Our PK at the person level was the sailor's service ID and was used for every table. While we had autonumber-style keys, this wasn't Access and we didn't have its restrictions to consider. The truth is, someone else controlled the service ID numbers so we simply had stored procedures in place to add someone to our records. I didn't control that, another person contributed a lot of the internal design stuff, and some of it was dictated from a server at BUPERS (U.S. Navy Bureau of Personnel). We only controlled those things for which we were declared the "official source" and anything else had to wait for the "official source" to act. I personally did a lot of monitoring and reporting for the DB's status and resource usage more than anything else. I also handled a lot of the security monitoring for anything (including ORACLE) that ran on my platforms or was accessed by my platforms. That was plenty.

 

[gemma-the-husky]

I generate this sort of file as script.txt , and then run it with ftp script.txt

you can try the commands manually. just type ftp to open the ftp utiilty and then it's all interactive.
start with open ftp_url and you should be able to work out the exact commands that do what you want

OPEN url reference
USER usename pwd
quote pasv
cd remote folder
lcd local folder
ascii
prompt
quote pasv
put filespec
quote pasv
ls * (list the files to make sure the put worked.
bye


But this is just to used to collect and send files to a ftp host, (eg, like filezilla) but not for interactive use. You can't use anything like onedrive or dropbox to host a shared database.

 

[adamgrracer]

Yes, in general, there are now a sufficient number of such programs with the help of which you can access several cloud storages, onedrive ftp or Dropbox, or some other alternative. For example, this is Commander One.