FTP using docmd.transferdatabase

I wish to transfer information from an office based MS Access database to/from a database located on an externally hosted web server to which I only have FTP access.

I think I can probably use the Microsoft Internet transfer control (msinet.ocx) to do the transfer via FTP but would prefer to keep things as simple as possible.

What I would like to know is if it is possible to use the DOCMD.TRANSFERDATABASE command to transfer information via FTP and if so what is the syntax for specifying the remote database in the command?





JulianBakerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
Im not aware u can do that, you can copy from machine to machine using TransferDatabase but then these two machines would be networked

I would imaging exporting table as csv, ftp it across them import into new table

u can find examples on using WinInet here
http://www.mvps.org/access/modules/mdl0037.htm

0
SidFishesCommented:
ITC is about your only solution...but your ftp idea isn't likely to work if you are trying to update the db...

what i use to update my web db is something like this

access form fills variables in an ITC url string or form variable
the url is a coldfusion page (but could be asp, php or whatever) which triggers a db insert or update and passes back the result of the insert to ITC for user feedback

i suppose what you could try is to ftp the data to a dir and then trigger something to import the data via some server sided scripting...


0
Gustav BrockCIOCommented:
This is off-line updating of data. If you are thinking of a "live" connection with some sort of linking tables from the external database, it is not possible as far as I know; you need either a drive letter or UNC path to link tables and - even if you could - it would be very slow.

As you only need to transfer one file, the easiest way is to export your selected data to a new database and then send that via FTP to your web-server to replace the old file.
However, if you need indexing of some tables in the database to transfer, you'll have to create these from your application either via VBA code or by SQL after the transfer.
If this sounds to much, you can create a master database with all the tables and indexes you need but without data. Then make a copy of this each time you will transfer data, link the tables from it, and fill it with data by running an appending query for each table.

If you need to import as well, download the file and link the tables to your application and run update or append queries as needed.

For the FTP transfer you can look up a bunch of solutions around with either a bunch of code or using third party ActiveX controls. For sending a single file, I use WCL_FTP (free) for which you can download a module for Access - it's nearly a plug-in and go:

  http://www.pacific.net/~ken/software/

Note the link on that page to the Access module.
This offers the user a small status windows to watch.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.