How to transfer my access query result .xls file to a FTP Server?

[Guest]

I got an application written in Microsoft Access (VB code).

I want transfer my Access Query result to a FTP folder.

DoCmd.TransferSpreadsheet method is works for if I want to transfer my query
as an Excel Sheet to a local folder.

Ex.
DoCmd.TransferSpreadsheet acExport, 8, strname & "A", “C:\temp\myFile.xlsâ€

But when I change this code to transfer my file to an ftp folder

For example.
DoCmd.TransferSpreadsheet acExport, 8, strname & "A", "ftp:// mostaff:
mostaff2sss@abcftp01/myFile.xls"

I get the following error

Sss Reports couldn’t close database ‘ftp:// mostaff:
mostaff2sss@abcftp01/myFile.xls’
Your last change may not have been saved because an internal buffer was
locked by another user(whose name was given in the previouse message).
To avoid losing data, wait for the other user to finish using the record,
and then click OK again.
If you Cancel, you may lose that haven’t been saved.



I was thinking to save the file in a local folder suchas C:\temp then write
the VB code to ftp transfer the file to the ftp server.

Can any please help me with this. I’m really struggling here. Any ideas also
appreciate.

Cheers

Raj

 

[Douglas J. Steele]

Access doesn't recognize the ftp protocol (nor http, for that matter).

You'll have to transfer the file to your hard drive, then transfer it from
there to the ftp server. Check
http://www.mvps.org/access/modules/mdl0015.htm at "The Access Web" for the
low-tech approach to doing this, or
http://www.mvps.org/access/modules/mdl0037.htm for a more robust approach.

 

[Guest]

Hi Douglas

My MS Access version is 2003. So I do not have the InetTransferLib Library
THe reference u gave me it says as follow.

"
To use this functionality in Microsoft Access 2000 or higher, please import
all the classes from the addin (which does not have the tables required for a
typical Access addin) in your own Access project. To use the classes, just
refer to them with the 'InetTransferLib.' prefix (eg: Dim objHTTP As HTTP"

Could u please tell me what it means by import all the classes from addin? I
can't see the InetTransferLib in my Access references

cheers
raj

 

[Douglas J. Steele]

Go to File | Get External Data | Import on the menu, navigate to where
you've put the MDA, select it and select all of the objects in it.

Alternatively, make a copy of the file and change the extension of the file
to .MDB and see whether you can open that.

 

[Guest]

I went to the FILE - > Get External Data -> Import

Then I Import the file "InetTransferLib.mda" (downloaded file from the
http://www.mvps.org/access/modules/mdl0037.htm

But still my code complains it can't find the "InetTransferLib"

Can you pleas help me with this.

cheers

raj

 

[Douglas J. Steele]

Exactly what did you import from the file?

If you're having problems, maybe you're best off sticking with the other
solution posted.

 

[Guest]

Modules - CDialog, FTP, HTTP, modMain.

The other solution is more work for the user. When I run that solution it
prompt me the command prompt/line (dos box) with ftp>
prompting me to type my ftp address. Is this is the way it suppose to work?

raj

 

[Douglas J. Steele]

That's not how the other solution is supposed to work, although I must
confess that it's been a long time since I used it, and I don't believe I've
ever run it using WinXP.

Did you create a file containing the FTP details, as it outlines at
http://www.mvps.org/access/modules/mdl0015.htm ? If you've done that
correctly, then they shouldn't be prompted at all.

 

[Guest]

U'r correct I just can't get it to work in XP.
The error give that C:/Temp/test.scr is not a valid Win32 application !!!!

cheers

raj

 

[Douglas J. Steele]

That makes no sense at all, since you're trying to shell to ftp.exe, not
test.scr.

Are you sure that you copied the code correctly from the page?