Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2005, 07:51 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using FTP Commands from Access 2000

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
Reply With Quote
  #2 (permalink)  
Old March 9th, 2005, 01:37 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old March 9th, 2005, 05:44 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply.

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

Many thanks for your time

Tim

Tim
Reply With Quote
  #4 (permalink)  
Old March 9th, 2005, 06:38 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 9th, 2005, 10:01 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old March 9th, 2005, 10:17 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old March 9th, 2005, 10:46 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old March 9th, 2005, 11:28 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
FTP Access Bose Visual C++ 0 October 9th, 2007 01:36 AM
Access database through oledb commands castor C# 1 April 26th, 2007 12:27 PM
SQL SERVER 2000 AND ACCESS 2000 ckentebe SQL Server 2000 3 June 17th, 2004 08:50 PM
ADE file in Access 2000 <---> Access XP ginoitalo Access 3 April 14th, 2004 09:06 PM
Access XP VBA compatibility issues w/ Access 2000 bourgeois02 Access VBA 1 August 19th, 2003 04:14 PM



All times are GMT -4. The time now is 05:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.