ftp transfer by vba ?

[zaankanter]

Yes, the file (preferably the .mdb file in wich the routine is called) should go to a prefixed place.
This is the situation:
My Acces-application is used by several users, each having their own stand-alone-version at dislocated places. To help/service them, they each have their own subdomain on my website, to wich they can upload their version of the application. In this way I can evaluate what their problem is and do updates. This is of course also a way to back-up.
This procedure is at present performed by calling ws-ftp, wich has for each of the users their user/pass pre-installed. But even though this is a fairly straightforward procedure, it still proves to be too complicated for some users.
That is why I want to fully automate this process: click=upload.
So yes, most of the data could be hard coded in each users version of the application (there aren't that many of them) or maybe taken from a table in wich they are stored.
Downloading is much simpler, I put the file in an windows-installer-wizard, wich the users can just open by clicking at the apropriate link in an email I send them.
Hope you have a better view of the probem now.
hope to hear soon,
Chris :)

[NeoPa]

You've lost me a little here Chris.

I asked if it is going to run the same file every time and you seem to reply "Yes", but you go on to say different users would be set up differently. This seems to indicate the answer should actually be "No".

What are the contents of the two files you anticipate using (CMD & FTP)? Feel free to * out any passwords or sensitive addresses. Just be sure that there are no items in there which vary under any circumstances.

PS. Sorry for the delay - I've been caught up with some problems yesterday & today.

[zaankanter]

You've lost me a little here Chris.
I asked if it is going to run the same file every time and you seem to reply "Yes", but you go on to say different users would be set up differently. This seems to indicate the answer should actually be "No".

Actualy I would be very pleased if we could come up with a simple hardcoded answer, because for each user the same values would apply over and over again, and would be willing to repeat hardcoding those values(with changes) in every copy of the application.
But of course a more subtle approach, with would save me that trouble would be even better.
I've done some digging and it seems to me that the Call Shell command does not give the possibility to transfer instructions tot the called program (e.g. ftp.exe)

What are the contents of the two files you anticipate using (CMD & FTP)? Feel free to * out any passwords or sensitive addresses. Just be sure that there are no items in there which vary under any circumstances

I describe the needs for 1 user, lets say the Amsterdam branch:
I would want to log in to ftp.cisteem.nl
For that a username and password (both always the same for the same version of the application, i.e. the Amsterdam branch).
Because the ftp-access to the site for this user (the Amsterdam branch) is restraint to the subdomain assigned to his version of the application (lets say amsterdam.ciste em.nl) this would right away log on to the right directory on the server.
Then a file, named cisteemuser.mdb should be transfered.
When hardcoded in the Amsterdam-branche version there would not be any variables
When coded in such a way that it would apply to al versions, the variables would then of course be:
Username
password
cisteemuser.mdb

PS. Sorry for the delay - I've been caught up with some problems yesterday & today.

Just very glad that you keep taking te trouble to answer a knitwit like me ! :)

[NeoPa]

Chris,

You keep adding bits of information without giving me all. The more bits you add the more I need to know to understand you.

Is the FTP job going to upload a single file to multiple locations (possibly with a rename)?

Is the name of the database going to change or do you already have multiple databases that will be uploaded?

I was thinking of uploading a single file to a single destination.

What I asked for earlier (the contents of the CMD & FTP files) would have answered all my questions (that's why that is what I requested).

I will post an example pair of files, but I need you to tell me what you have in mind really. I suggest we leave multiples until at least we've dealt with a single one. Use Amsterdam as the first example if you like.
CISTeam.Cmd

ECHO ON
CD /D "C:\Database"
"%SystemRoot%\System32\FTP.Exe" -n -s:CISTeam.Ftp -w:16384 ftp.cisteam.nl

CISTeam.Ftp

user ??? ???
binary
lcd C:\Database
cd ???
bell
put CISTeamAmsterdam.Mdb
bye

Now, this may not be right for you, and there are places where I really have no idea what you need and I've just used "???". This is why I need you to post the files you are considering using, which will give me clearer information of what we need to do. Does that make sense?

[NeoPa]

If you find any of what I'm saying confusing or complicated just let me know. I can help if I know.

I can't help if I'm short of the correct information.

[zaankanter]

Hi,

I suppose there is just too much wich I just don't grasp. That makes it difficult for me to understand what it is exactly that you ask me. I agree that its probably best if we try to solve the problem as a one-file-one-adress problem.

To make things more realistic, I have made an ftp-account for Amsterdam that actualy works (for as long as we need it for the purpose of solving my problem):
ftp.cisteem.nl
user amsterdam#ciste em.nl
password orange

First of all I'll give you the code, adapted as much as I can to what I think is needed:

CISteem.Cmd
Code: ( text )

ECHO ON
CD /D "C:\CISteem"
"%SystemRoot%\System32\FTP.Exe" -n -s:CISTeem.Ftp -w:16384 ftp.cisteem.nl

CISTeem.Ftp
Code: ( text )

user amsterdam#cisteem.nl orange
binary
lcd C:\Database
bell
put CISTeemAmsterdam.mdb
bye

I left out the

cd ???

part, because the ftp-rights of amsterdam are restricted to a single subdomain, and the working directory of the server will automaticly be the one assigned to amsterdam.
I hope this is what you need.

Now some questions from me (sorry for the ignorence):
I think I understand that CISteem.ftp contains commandlines much the same as would be typed into the ftp.exe window, after giving the command "open ftp.cisteem.nl"
And it seems to me that CISteem.cmd invokes those commands by calling CISteem.ftp
What I don't get is how you get CISteem.cmd to work from a click-event like

Private Sub Knop75_Click()
On Error GoTo Err_Knop75_Click
 
    Dim stAppName As String
 
    stAppName = "C:\WINDOWS\system32\ftp.exe"
    Call Shell(stAppName, 1)
 
Exit_Knop75_Click:
    Exit Sub
 
Err_Knop75_Click:
    MsgBox Err.Description
    Resume Exit_Knop75_Click
    End Sub

Hope you enjoy this neverending saga as much as I do :)

Chris

[NeoPa]

Almost perfect Chris. You have come along almost the whole distance in that one post.

NB. This is more from doing than talking ;)

From this I can tell that your database is C:\Database\CIS TeemAmsterdam.M db and your two command files are C:\CISTeem\CIST eem.Cmd & C:\CISTeem\CIST eem.Ftp.

If that is all correct, then try simply changing line #6 of your VBA code to :

stAppName = "C:\CISTeem\CISTeem.Cmd"

Let me know how you get on.

PS Neverending sagas are most enjoyable when they produce results ;)

[zaankanter]

It works like a charm !!!
This is exactly what I had in mind when I first posted my question.
Besides solving my question, I feel I've learned a lot about cmd and ftp.
Thank you very much!!!

One remaining question:
- It seems to me that the (shell ?) routine runs seperatly from the acces-application. Is there nevertheless a way in wich I can inform the user (e.g. a message-box) when the transfer has been succesfully ended?

yours gratefull
Chris

[NeoPa]

It works like a charm !!!
This is exactly what I had in mind when I first posted my question.
Besides solving my question, I feel I've learned a lot about cmd and ftp.
Thank you very much!!!

Very pleased to hear it. That's the idea of course, to include learning in with the answers so we're not just providing ready-made solutions. Solutions is good, but with education is better :)

One remaining question:
- It seems to me that the (shell ?) routine runs seperatly from the acces-application. Is there nevertheless a way in wich I can inform the user (e.g. a message-box) when the transfer has been succesfully ended?

yours gratefully
Chris

It sounds like you need the Shell() function to work synchronously. Unfortunately it is an asynchronous function so returns control immediately the other process has been started.

See Open Access mdb from mdb delete error for a synchronous version called ShellWait().

[zaankanter]

Hi

Doing ftp-transfer in the way described above I stumbled upon another minor problem. I don't know if it should be posted here or if it should be a different posting. Anyhow this is the problem:
Al goes well when the transferred file is relatively small, but when I send a file of 5/6 Mb this happens: The file does transfer (and of course takes some time doing so) but instead of giving a message back that the transfer succeeded (wich would make the routine above do "BYE") the server stays silent. After some considerable time there is a time out and the routine (see above) finaly does BYE.
When I check, the file did arrive at the server allright.
So the routine works allright, but the ftp-window stays open far too long (up to 10 minutes or so).
Is there anything I can do to remedy this? Or is it something the server does?

Happy to hear any comment.
Chris

[NeoPa]

I'm not sure I can help you on this Chris.

I expect it is related to the FTP server being used on the site you're connecting to, but pure supposition really. All I can say is that I haven't experienced that problem.

FTP isn't a Windows protocol per se, but you may want to post in there or even in Linux / Unix / BSD to see if anyone there has experience that can help. If you get no joy you could try Miscellaneous Questions, but there are fewer experts who frequennt that forum so you may struggle to find an answer.

Anyway, good luck.