Since the points of action are on two different servers, you can break this problem down into a few discrete steps as a "divide-and-conquer" method. The divisive approach helps you break things down into achievable small parts that can then be reassembled into a functional whole.
Last bit of physical clarification: Do the two separate servers have a hard-line connection capability to each other? I.e. these are two different servers for two different companies. But since they are the same owner (YOU) are they in the same building or physically adjacent buildings?
Where I am going with this is that if you could establish a relatively short-run Ethernet connection between them, you could DIRECT-CONNECT the two databases to each other and never get involved with mail or FTP.
IF this cannot be done, but E-mail is still possible, then break this up this way:
1. Establish a format of some spreadsheet for A to send its transactions to B and for B to sent its transactions to A. As part of this, establish a naming convention for files such as the AtoBOrders or the BtoAUpdates. Perhaps with a fixed prefix and a date/time as part of the name. Then, using the FileSystemObject, you can tell Access to find files with names like AtoBOrders_*.XLS (the * being the wildcard). Or BtoAUpdates_*.XLS, for the other direction. In other words, you are building a "protocol" of data transfer.
2. Establish queries for each output so that you can do an Export to Excel. You want these queries to give you the rows of data that you need for the "other side" to be able to import the transactions. This query needs to have a table waiting on the other side (OR needs to be temporarily mapped as a table) so that the receiving side can bring it into Access, process it, and then abandon it. IF you can do it, the temporary mapping, though SLIGHTLY harder, will be less impacting on Access and general system resources. Not to mention that if you then break the connection once all has been processed, you can even use the FileSystemObject to rename and do a .MoveFile to a backup folder in case you need to review something or otherwise keep it separate as a record.
3. Decide which way you want to transfer the file between A and B, which could now be either E-mail or FTP or even the "thumb-drive" network - what us old-timers used to call "sneakernet."
3.a. IF it is possible to see each machine's directories from each other, you can use FileSystemObject to just DROP the file into a predetermined area on the other server. This might require your IT support team to help you set up the right kind of network permissions, but that should be a one-time action if that is what you do.
3.b If you must use FTP then you will need to look for a package that lets you know that you sent the file successfully, which AT WORST will involve trapping the FTP log file and reading it to look for a specific type of success message.
3.c. If you can use E-mail with a package (like Outlook) that lets you see incoming mail, then the trick will be that the sender must build a message with a who-cares text body, but it will have a very specific subject line and the file will be an attachment to the throw-away body. On the other side, you have to have a reader that looks in the INBOX for specific subject lines and processes those one at a time, extracting the attachments (see my #2 above).
There, that is the overview of how to approach this from 4 different ways - direct-mapping if possible, and 3a, 3b, 3c as ways to send the files.
