FTP an Access MDB

[Infocore]

I have a program where my Access MDB creates a new MDB file and
exports certain into it. It then sets a database password and some
other features. The user needs to fTP this file to a designated
location. Currently we have the users opening up WS_FTP and doing the
FTP on their own, I'd like to build it into my code so after it
creates the MDB it FTPs the file for them.

I am attempting to use code that I took from previous posts but am
running into an error. Although the file is sent by FTP and I can see
it on the site, it is 0KB and when I download it and try to open it, I
get a message that states "unrecognized format". Can anyone take a
look at this code and see if they spot anything that would cause this?

Public Function UploadFTPFile(sFile As String, sSVR As String, sFLD As
String, sUID As String, sPWD As String) As String

Dim sLocalFLD As String
Dim sScrFile As String
Dim sSource As String
Dim iFile As Integer
Dim sExe As String

Const q As String * 1 = """"

DoCmd.Hourglass True

On Error GoTo Err_Handler
sLocalFLD = CurrentProject.path

' will break if empty folder exist so error to pass
' must create folder first, so API calls work
On Error Resume Next
If Dir(sLocalFLD & "\") = "" Then MkDir (sLocalFLD)
On Error GoTo Err_Handler

sSource = q & sLocalFLD & "\" & sFile & q
sScrFile = sLocalFLD & "\upload.scr"
If Dir(sScrFile) <> "" Then Kill sScrFile

' Open a new text file to hold the FTP script and load it with
' the appropriate commands. (Thanks Dev Ashish !!!)
iFile = FreeFile
Open sScrFile For Output As iFile
Print #iFile, "open " & sSVR
Print #iFile, sUID
Print #iFile, sPWD
Print #iFile, "cd " & sFLD
Print #iFile, "binary"
Print #iFile, "lcd " & q & sLocalFLD & q
Print #iFile, "put " & sSource
Print #iFile, "bye"
Close #iFile

sExe = Environ$("COMSPEC")
sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
sExe = sExe & "ftp.exe -s:" & q & sScrFile & q

ShellWait sExe, vbHide
DoEvents

Exit_Here:
UploadFTPFile = GetFileText(sScrFile)
DoCmd.Hourglass False
MsgBox "File sent FTP", vbOKOnly, "File Complete"
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "E R R O R"
Resume Exit_Here
End Function

 

[Douglas J. Steele]

Nothing obvious jumps out. What's an actual content of your upload.scr file
once you've written it? What happens if you try running ftp.exe in a DOS box
using that script? What happens if you try running ftp.exe in a DOS box
manually passing it each of the arguments from upload.scr?

 

[Albert D. Kallal]

One thing you have to check is that you delete the file, as overwrite
will not occur...

So, *right* after you cd command, I would go:

Print #iFile, "cd " & sFLD

Print #iFile, "Delete name of file with no path goes here"

 

[Infocore]

The file looks like this:

open ftp.infocore.us
(login is correct)
(pass is correct)
cd Data
binary
lcd "F:\InfoCore\InfoCore_Clients\HirshlAssociates"
put "F:\InfoCore\InfoCore_Clients\HirshlAssociates
\ROBBUO-05-04-2007-2253.mdb"
bye


The login and pass are correct I have just removed them. I do run
into an error on the lcd line that says it can't find the folder. I
removed the line and it seems to work the same. The file is sent by
FTP and to the right location. I just can't understand why it is 0KB.

Robby

 

['69 Camaro]

Hi, Robby.

I just can't understand why it is 0KB.

Open an FTP session manually so that you can troubleshoot your FTP script by
typing the commands line-by-line that your script uses.

cd Data

Perhaps the file
<PathToHomeDirOnRemoteServer>/Data/ROBBUO-05-04-2007-2253.mdb already
exists? The default is to overwrite existing files, but your put command
won't overwrite an existing file if it's locked by another process.

First check that you're really in the directory you think you are on the
remote server with the PWD command. If you are, then try deleting the file
on the destination server. (You were planning to overwrite the file anyway,
so it doesn't matter if you delete it first. Correct?) If you can't delete
it, then you'll receive an error message. It probably won't tell you why
the deletion failed, so you'll have to investigate further on the remote
server's end. If the deletion failed, I would check first whether the user
on the remote server has read, delete, and write permissions in that
directory.

lcd "F:\InfoCore\InfoCore_Clients\HirshlAssociates"

Perhaps you meant the following command?

LCD F:\InfoCore\InfoCore_Clients\HirshlAssociates

put "F:\InfoCore\InfoCore_Clients\HirshlAssociates
\ROBBUO-05-04-2007-2253.mdb"

I can't tell whether your script has this on two lines or the line wrapping
in my newsreader is doing it. It should be only one line in your script,
but to easily work around this, perhaps you meant the following command?

PUT ROBBUO-05-04-2007-2253.mdb

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Susie Johnson]

SQL Server has FTP functionality included

It is reccomended that you use DTS for these types of activites