Import remote csv file

[Matthew]

I am trying to automate importing a .csv file from my web site to Access.

When I try using a TransferText macro, I get an "Internet Logon Failure".

I have added the ftp site to my ftp locations with the correct user name and
password, but that didn't help any.

Another try was to use a .bat file to ftp the .csv file to my computer. It
looks like adding a RunApp action to the macro allows me to do this, but it
doesn't wait for the .bat file to finish.

So, I would like help either making Access ftp the file to import, or make
it wait while my .bat file gets it.

Thanks in advance,

Matthew

 

[Douglas J. Steele]

There's code at http://www.mvps.org/access/api/api0004.htm at "The Access
Web" that will make Access wait until the batch job is finished before
continuing.

Another option is to use the Internet Data Transfer library, as shown at
http://www.mvps.org/access/modules/mdl0037.htm

 

[Joe Fallon]

Also, Access cannot directly use FTP even though it looks like it can.
It has never worked in any version.

I have used both solutions posted by Doug. They work fine.

 

[Matthew]

I have tried the following; it brings up my .bat file, but when it finishes
it does not continue.

I have also tried it without the "Call Shell" line, it does not open my .bat
file. It hangs as well.

Any help would be appreciated.

Matthew

Module1:
Function importtest()
Call Shell("""C:\Documents and Settings\Matthew\ _
My Documents\Work\ColdFusion\test.bat""", 1)
ShellWait ("C:\Documents and Settings\Matthew\ _
My Documents\Work\ColdFusion\test.bat")
'Code for importing
End Function

Module2:
Code from http://www.mvps.org/access/api/api0004.htm

 

[Matthew]

Figured it out.

Thanks for pointing me to this code.

Matthew

 

[mdmilner]

Specifically regarding http://www.mvps.org/access/modules/mdl0037.htm
I don't have any problems getting the Shell option to work - and righ
this minute I have a SendKeys ftp option working; at least it seems t
be working but I'm skeptical.

I'd really like to find a way to stay inside VBA and avoid creating ne
batch files if at all possible (shell/batch seems to be my current mos
reliable option)

I found the above Dev Ashish program but couldn't get it working.
It's choking on the Reference to the mda file and I can't seem to ge
it to find it. The debugger actually fails on the very first variabl
declaration:

[vb]
Dim objFTP As InetTransferLib.FTP
[/vb]

As you'd expect from a 'missing' reference.

I'm on Win2Kpro using Office2Kdeveloper.
Any thoughts? I've really never tried using mda's/add-ins before s
it's quite possible I'm missing something basic. Even tried usin
regsvc32

Mik

 

[Marc]

[vb]
Dim objFTP As InetTransferLib.FTP
[/vb]

As you'd expect from a 'missing' reference.

I'm on Win2Kpro using Office2Kdeveloper.
Any thoughts? I've really never tried using mda's/add-ins before so

Run the program. When the error comes up, go into debug mode. Click the stop
button. Click on Tools->References. This will show you which libraries are
referenced, and which one is missing. Check lower down in the list as to
whether you have a more recent version on your machine.

HTH
Marc

 

[mdmilner]

Turns out the fix was to convert the mda from A97 to A2k - delete the
reference then manually browse to add it in. Could have sworn I'd
tried that before posting.

Mike