Hi I don't know if this is possible. I have an Access 2000 database which imports text files using saved import specifications. These test files are obtained by using ftp from a local server. eg from command prompt.

For example, type : ftp 138.9.99.99
login using a username and password
change directory : cd /home/foc/focext
then use "get" to get the required files.

Is there any way I can automate this from within a code module in access?

I have heard of using dos shell commands but I am a complete novice to this.

Any help is greatly appreciated.

Many thanks

Tim:)

Tim

__________________
Tim

The technique is rather simple. To use my technique you'll need:
1) A bat file to run the FTP
2) A text file that contains your FTP commands
3) A call in a VBA module to run the bat file.

The bat file is pretty simple. Do something like:

@ECHO OFF
REM Set up for FTP transfers
c:
cd \_the path where you want your files to arrive_
c:\windows\system32\FTP.exe -s:Fetch.txt

Fetch.txt (in the last line above) contains the commands you normally manually type if you use command line FTP. E.g.

verbose
open _your ftp site_
_your user id_
_your password_
cd _path to folder containg file to download_
binary
prompt
mget _file name_
quit

In your module code:

Shell "c:\_path to bat file_\Fetch.bat", vbNormalFocus
MsgBox "When the FTP has completed, click OK to continue.", vbOKOnly, "Wait for Download"

The "Shell" will open another window and "attempts to set focus" to that window. The thing is, it won't "lock" the focus. The user can click the Ok on the prompt and your code will start processing as if the FTP is already complete. Your users may need some training to understand what the Msgbox prompt means.

Keep in mind, if you use this technique the "Fetch.txt" file is going to contain a record of your password that anyone will be able to see if they know where to look. It's not really great for security in a multi-user environment.

If your users specify their own id and password, you could build the Fetch.txt file just before "Shelling".

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks for the reply.

I will give it a try and get back to you.

Many thanks for your time

Tim

Tim

Hi,

I attempted your srcipt and I dont think Ive grasped it fully. This is what I have done so far....

1) Ive created the batch file which contains :

@EHCO OFF
REM SETUP FOR FTP TRANSFERS
C:
CD \FTPDUMP 'where I want files to go
C:\WINNT\SYSTEM32\ftp.exe -s:fetch.txt



I have then created fetch.txt which contains :

open 138.4.24.10
user
password
cd /home/foc/focext
binary
prompt
get inpscep
quit

I have tried running the batch file from direct from windows. I get a quick flash of a window opening and closing again and nothing else. Should I have to run it from the code module to work???

Thanks in advance.




Tim

It's possible that your FTP.EXE isn't in the folder specified.
Check to see where your FTP.EXE file is located.

Another possiblity is that you mis-typed the folder to change to. BTW, you can't put a comment after any of these commands. The only way to comment your code is to use REM on a line by itself. I don't know if your CD command actually has the "'where I want files to go" at the end of it or if you added that for my benefit.

The last possibility is that the Fetch.txt file isn't in the folder you changed to.

One way to get more information about what is happening would be to open a command window and run the bat from there. The screen won't do that quick flash if you do that. Also, if you put REM in front of the @ECHO OFF you will see each line as it is executed.

Other than that, I'm not sure how to help.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks again for the advice. I tried what you suggested and ran the batch file from the command prompt and it worked fine. However, when I try and run it through vba (ive got a button on a user form within accesss) it doesn't work. My access code is as follows :

Private Sub Command0_Click()
 Shell "c:\ftpdump.bat", vbNormalFocus
 MsgBox "When the FTP has completed, click OK to continue", vbOKOnly, "Wait for Download"
End Sub

Any thoughts???

Thanks

Tim

Hmmm...

Maybe try setting an error trap in your procedure. This will make sure that you don't have some other error trapping that isn't giving you a message.

Then try...

debug.print shell("C:\ftpdump.bat",vbNormalFocus)

This will display in your Immediate windowt he number of the process that was created to run the bat file.

I'm not sure if this works and don't have time to try it. But you might try putting a PAUSE in your bat file after the FTP statement. This might keep the command window open for you to see what happened.

Also, do be sure that it truly isn't working. I've been caught by things performing faster than I expected or performing in the background, thinking they didn't work when in fact they did.

Oh. One possibility is that you have virus software that won't allow a program to Shell a bat file. You might try shelling some other application to be sure this isn't the problem.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Hi again,

After using a 'pause' the following info was displayed in the command window:

c:\Documents and Settings\INFORMATION\My Documents>

which told me that it was trying to start the process from this directory. So I places a cd\ in the first line of the batch file to send to focus back to the root.

Hey presto.....

Many thanks for you help (and time!!)

Tim

Tim