Batch file doesn't run through Access

[CarrieR]

Hi everyone,
I see this issue repeated in a lot of forums, but so far no answers have worked for me.

I am trying to get Access to run a .bat file (also works as a .cmd file), using a simple Shell statement, but it isn't working - it seems to rush through it too fast. Starting the file manually outside of Access works perfectly.

The basic code:

Shell "C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\ftptransfer.cmd", vbNormalFocus

(In case it matters, the bat file logs in to our ftp server and downloads a specific file...)

I've tried adding various waits after the code, but it doesn't seem to make a difference, like:

Shell "C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\ftptransfer.cmd", vbNormalFocus
Dim Counter
Counter = 0
While Counter < 300000000
    Counter = Counter + 1
Wend
Debug.Print Counter

...and other "wait" type of solutions, but that doesn't seem to be the issue - the cmd window closes well before it starts the counter.

It seems like the code is moving on or ending before the batch file has time to run (it takes about 1 full second). Or is it another issue?

[Rabbit]

Try Shell """C:\Docum ents and Settings\myname \My Documents\CLIEN TS - ACTIVE\ftptrans fer.cmd""", vbNormalFocus

[CarrieR]

No, didn't work - the issue isn't in the quotes...

[CarrieR]

Actually, just found what is happening, and not sure why...

It seems like the bat is actually downloading the file to:
C:\Documents and Settings\myname \My Documents\

I have absolutely no idea why, unless all of the spaces in the file path is making things complicated?

I can fix with a line moving the file, but I sure would like to understand why this is happening (only when I run it with Access this happens....)

[Rabbit]

Well, it doesn't matter that the function ends before the batch file finishes running. The problem is somewhere else. I have no problem with the code on a batch file that I created that takes 15 seconds. I assume you used myname on purpose to disguise your user ID?

[ADezii]

@CarrieR - try the API approach

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Dim strFile As String
Dim strAction As String
Dim lngErr As Long

strFile = "C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\ftptransfer.cmd"
strAction = "OPEN"

lngErr = ShellExecute(0, strAction, strFile, "", "", 0)

'Optionally, add code to test lngErr

P.S. - The Shell Function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.

[CarrieR]

The API approach, like the simple shell script, is still putting it in
C:\Documents and Settings\myname \My Documents\
rather than the directory I specify in the bat file.
(and yes, it's not literally folder "myname")

From some more investigoogling , I'm guessing it has to do with the MS Access default settings (Access Options > Popular > Default database folder). Which seems like it's just a quirk to Access - unless anyone has an explanation/solution to it besides the obvious changing of the default settings.

In the meantime, I'm just going to "solve" the issue by moving the file after it puts it in the My Documents folder. And thanks for the reminder, ADezii, I'll make sure there's enough of a pause between actions, or just keep them separate.

[NeoPa]

I do a lot of this, and the surprising thing for me so far is that the contents of the CMD file has neither been explained nor shown.

So far, I'm guessing with very limited information, but it seems to me you are expecting an FTP downloaded file to arrive in a particular place, but it ends up somewhere else instead. If so, the problem is almost certainly down to the contents of the CMD file (and any FTP file you use too of course). Not an Access issue per se, but I'm happy to have a look at them for you if you post them.

As far as the asynchronous nature of the Shell() call goes, I have a short article on how to get around that (ShellWait() Function) if that is necessary. It seems it probably is from my inference, but much is unclear here so I'm just guessing.

PS. To be clear, I do appreciate that every attempt has been made to give as much info as seemed to be needed. I expect the missing info was simply down to a lack of realisation as to its critical nature. I don't intend to criticise the question, or the OP, but simply to indicate where I suspect the problem(s) lie.

[CarrieR]

The cmd (or bat, either works the same) file hasn't been posted, because running alone, it posts perfectly to the correct spot. It is only if I run it with Access that it posts to the wrong place.

Therefore, it's an Access issue. Yes?

[CarrieR]

Though for completeness' sake, the bat/cmd file I run has this:

ftp -i -s:"script.txt"

script.txt has this written:

OPEN [ftp server name]
[user name]
[password]
binary
cd [ftp file location]
MGET [file name]
BYE

(Everything secure is in brackets, of course)

Runs perfectly each time, without fail. I'm not saying it's an Access "issue", just that it seems to go to Access default locations. This is something I've seen hinted at at other responses too.

[NeoPa]

If I thought that were true I wouldn't have posted, so No. There are many reasons why it may work from one place and not another, some of which are not related directly to the fact that it is invoked from Access. I don't want to go to the trouble of thinking through all the possible reasons and explaining them to you when I could see it a lot more easily if I could see the contents of your files. Another piece of information that would be instructive would be a clear explanation of exactly how you run the files when you run them alone. If from the command line then please indicate the default folder run from. If a shortcut then those details also needs to be passed. While I'm about it, posting the VBA code you use to invoke it from the Access database would also be helpful.

Clearly this isn't such an obvious problem that you've been able to work it out yourself. Please don't expect me to do it without even any information to work with. I can assure you that I have never had problems getting FTP jobs to work correctly when called from Access, so it's not a problem that has no solution. Only the information is still missing.

[CarrieR]

OK, so it's posted. I guess I don't understand the question of how I run the batch file. I literally click it, it runs. The whole thing is posted above.

What information is missing?

[NeoPa]

CarrieR:
Runs perfectly each time, without fail. I'm not saying it's an Access "issue", just that it seems to go to Access default locations.

You beat me to the punch there Carrie. I suspect you're right about it using the Access default locations, but your files can easily be updated such that this ceases to be an issue.

Forget the other info. I wanted to see this to confirm my suspicions and it has. I just need to know exactly where the file needs to be FTPed to. The extra line in the FTP file (script.txt) will use lcd (Local Change Directory). Always something you should use in FTP files unless you specifically want the destination to be relative to the calling position.

[CarrieR]

I tried adding a lcd statement before and after the one line of my batch file - both cases, it still went to the Access default directory (C:\Documents and Settings\myname \My Documents\)

lcd "C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\"
ftp -i -s:"C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\script.txt"

Is there something else I need to do to get it to be recognized?

[NeoPa]

You misread my post it seems. It's the correct command, but it needs to be in your FTP file (not the CMD file). See example below :

OPEN [ftp server name]
[user name]
[password]
binary
lcd "C:\Documents and Settings\myname\My Documents\CLIENTS - ACTIVE\"
cd [ftp file location]
MGET [file name]
BYE