Hi there
I have been having constant problems solving this problem for my
employer.

We have a windows 2000 server connected to the internet at our office
by a 64k isdn line - this hosts an access 97 database (.mdb) which is
shared across our intranet to the clients. Every evening, we need to
upload this database to our ISP's ftp server to update our pricelist
and stock list etc. I have tried to find a reliable piece of
commercial software that will do it but the only one I found kept
crashing and filling up the event log with restart messages. I have
also tried scripting with ftp.exe and batch commands and the task
scheduler but this is both insecure (password in plain text sitting in
a text file on our server) and unreliable. The solution will need to
happen regardless of the logged-on status of our server (ie it must
either reside at the ISP's end or run as a service). Another issue is
that our connection is an isdn dialup that is scheduled at the isp end
to disconnect every 2 or 3 hours. There must be a better way to do
this - I just need someone to give me a new standpoint. Thoughts
anyone?

Rather than uploading the .mdb via FTP, why not keep a different
database on the web server, then schedule a script to query the data
on your intranet and update the data on the web server?

The issue here is that we do not have direct control over the server
at the provider end so scheduling the query and update is difficult if
not impossible.

tom,

>> I have
>> also tried scripting with ftp.exe and batch commands and the task
>> scheduler but this is both insecure (password in plain text sitting
in
>> a text file on our server) and unreliable.

Note that ftp by default transmits those in clear text format anyways;
so regardless ftp is almost never a secure solution.  With this in
mind, I would be cautious of placing your .mdb files in the path of an
ftp service.

Concerning your problem... do you have any control at all over the
server on the other end?  You mentioned in your original question that
the solution would have to run on the upstream server or as a service.
 If you could place a web  page for instance on the server on the ISP
end you could "roll your own" DTS type of scenario whereby you could
package up any changed records (or all recs) in an xml doc, post to a
web page.  The web page could then tear through this "updategram" and
transactionally update its local db.

To get around the sometimes-on nature of your connection you could do
all this from within a VB or C# client app; detect if the connection
is present, do the post, log a success msg.  This could be on a timer
to mimic a service like app that could also run on a client like 98/Me

Of course you could also just post the entire db to the server the
same way without doing updates and such but you might run into timeout
and/or size limits for posting size.  For this check into a product
like fileup from SA.

At least this way you could have more control over access and have a
transactional process.  There would also be no scheduling issue as it
is a push driven model.

Hope this helps

CuteFTP Pro (http://www.cuteftp.com/products/cuteftppro/index.asp) is
a good secure FTP program having scheduling options.

As you have identified, FTP is not secure for this application.

I would use pscp, part of PuTTY, from
  http://www.chiark.greenend.org.uk/~sgtatham/putty/
if your server runs SSH. Probably not, though, since it sounds
like you are using Microsoft Windows.

An alternative would be to write a CGI script on the server
which authenticates you via some mechanism and allows you to
upload the database. A counterpart script on the client will
send the database to the server, and can be scheduled to
run periodically.

If you are unhappy with the idea of the password sitting in
a file on the server, you have something of a problem, since in
any automated system, enough information to authenticate the
client against the server must be present on the client.
Otherwise human intervention will be needed for every upload.

If you are in fact on a windows platform which you probably are,
unless you are using chillisoft asp or the similar in which this would
apply as well.  If you are using unix use perl ;)

you can write a script that will upload a file to the webserver using
windows scripting.  Using the XMLServerHTTP object (I think that is
the name) a part of MSXML 4.0 you can securely post a request to a
webserver.  you can get a base64 function somewhere (google) on the
internet, use it to encode the file you read with the
Scripting.FileSystemObject.  Take this base64 encoded file, which will
be about 4:3 larger, and add it to the request as a post parameter.

On the webserver take a base64 decoding function and decode the post
parameter, write the file to the server.

You can use this over https i believe and you can use HTTP
authorization.

if you write the script in vbs you can "encrypt" the password in the
code for the script. this is not secure because it is a simple
algorythm to decode (they should have just made it the tokenized
version of the code, it isnt human readable either) but this can offer
a way to not have the password in cleartext.

If you have .net available on both servers you can write a script that
uses encryption.

Keep in mind that just about any solution can be come about with a
little scripting.  if you can write it in asp you can write it,
without limits, in the shell.  Its just com objects, and i would be
willing to bet you can find a ftp client object.

Thanks for the help on this one everyone - my coding not being as good
as perhaps it could be, i've opted to write a Windows Scripting Host
(cringe) .vbs script (pretty much identical to ASP) that does a hybrid
of the things everyone has said really - it skims off the changes to
the full database and cuts out all of the unnecessary records and
fields (non-stock) into a much more manageable sized mdb, then FTPs
that file out to the webserver using ServerObjects.com's free ftp
client COM component. That script is scheduled to run nightly on the
local server, and because I'm writing in VBS, it is MUCH easier to do
error trapping and handling than with simple batch files. I've found
this an excellent exercise for my programming skills and have picked
up a few new techniques on the way. If you want to see the end result,
have a look at www.unimogs.co.uk. Thankyou for your help.