Problem with calling a .bat file from MS Access in VBA - Access switches directories

[AAAndy]

I have all my files including the Access file that I use in the following directory \\as65\data\Info\Ple\ConvFile.bat
It is on a server.

I have the below code that accesses the .bat file from MS Access

Shell "cmd /k ""\\as65\data\Info\Ple\ConvFile.bat""", vbNormalFocus

When I run the ConvFile.bat by clicking on it it runs fine.
But when the above code is run from vba it changes the directory to Access Default Database folder on my desktop which is in options of MS Access. The .ConvFile.bat file is not searching in the current directory.

For example. The below code in .bat searches for a .TXT file on the Access Default Database folder instead of the current directory \\as65\data\Info\Ple\

for %%x in (*.TXT) do set /a count+=1

Is there anyway to fix this?

[ranman256]

what changes folder? the command window?
it doesnt matter what folder you are in if the full path is given to run the .bat file.

if you need the path set in the bat file code, then CD to the folder you wish.

[AAAndy]

When I click on the ConvFile.bat file

then it runs the for %%x in (*.TXT) do set /a count+=1 with no problems in the current directory

but running it from access the .bat file searches it in Access Default Database folder that is on my desktop

[Micron]

couple of thoughts: I don't see any spaces in the directory path, so /c should work, perhaps better than /k, which may even negate the need for nested quotes.
more importantly perhaps, I think your problem is due to using a UNC path in your directory string, although I would have suspected you'd have received an invalid path error. Perhaps there is something about your .bat file (such as suppressing warnings) which would explain that. I'm no expert in command line files, so it's not likely I could help much. I simply recall issues that I had with UNC paths in .bat files.

[ssanfu]

It has ben a long, long time since I've had to write batch files.

I did find a site that suggests you should try something like

Shell("cmd /k cd \\as65\data\Info\Ple & ConvFile.bat", vbNormalFocus)

or

Shell("cmd /k cd /d \\as65\data\Info\Ple & ConvFile.bat", vbNormalFocus)

but the site had talked about a mapped drive letter.....


In Windows, you might try mapping a drive letter to the UNC path - say "T", then use

Shell("cmd /k cd T:\ & ConvFile.bat", vbNormalFocus)

or

Shell("cmd /k cd /d T:\ & ConvFile.bat", vbNormalFocus)

It's been sooooo long....

[Micron]

From my archives: the first line sets the directory to the current location, but to whatever the user's assigned drive letter is, so this is how I eliminated the need to use UNC paths.
set MyDir=%~dp0
the next line uses that value by reference
cd %MyDir%

Thought that might help, though I don't know if the OP must use UNC path or if it was being done because of different users having different native drive letters.

[AAAndy]

It has ben a long, long time since I've had to write batch files.

I did find a site that suggests you should try something like

Shell("cmd /k cd \\as65\data\Info\Ple & ConvFile.bat", vbNormalFocus)

or

Shell("cmd /k cd /d \\as65\data\Info\Ple & ConvFile.bat", vbNormalFocus)

but the site had talked about a mapped drive letter.....


In Windows, you might try mapping a drive letter to the UNC path - say "T", then use

Shell("cmd /k cd T:\ & ConvFile.bat", vbNormalFocus)

or

Shell("cmd /k cd /d T:\ & ConvFile.bat", vbNormalFocus)

It's been sooooo long....

This did not work

[AAAndy]

From my archives: the first line sets the directory to the current location, but to whatever the user's assigned drive letter is, so this is how I eliminated the need to use UNC paths.
set MyDir=%~dp0
the next line uses that value by reference
cd %MyDir%

Thought that might help, though I don't know if the OP must use UNC path or if it was being done because of different users having different native drive letters.

I put the below in my .bat file and it worked

set MyDir=%~dp0
cd %MyDir%
Pushd %MyDir%

[AAAndy]

but now I get the below error. Does anyone know how to fix the error?

Run-time error '3011':


The Microsoft Access database engine could not find the object "Temporary'. Make sure the object exists and that you spell its name and the path name correctly. It 'Temporary' is not a local object, check your network connection or contact the server administrator.

vba code stops on this line DoCmd.RunSavedImportExport "Import-temporary"


if I click debug and then f5 continue then it completes and uploads the file

    Shell "cmd /k ""\\as65\data\Info\Ple\ConvFile.bat""", vbNormalFocus
    
    DoCmd.RunSavedImportExport "Import-temporary"
    DoCmd.RunSavedImportExport "Import-tempTitle"
    DoCmd.OpenForm "Display All"
    DoCmd.Close acForm, Me.name

[Micron]

Possibilities:
- the bat file hasn't finished running, thus the environment necessary to handle the next line has not been created. If that is the case, you might need to introduce a pause to allow that to happen. Can't say for certain since I don't know exactly what the bat file does. You could test that theory by stepping through the code, allowing what you think is a sufficient pause before attempting to execute the next line.

Error generally means Access cannot resolve your reference to the named object, usually due to being mis-spelled, no longer exists, can't be found or incorrect syntax.
- check that the export-import has been created in the database used to run this code and that the database is in a trusted location. It can fail due to that also.
- if the import/export deletes a table, I don't think it's possible to call from code (not sure)
- the named specification may have to be enclosed in quotes ("Import-temporary")
Or the destination for the export/import is invalid or not trusted

That's all I've got...

[AAAndy]

Possibilities:
- the bat file hasn't finished running, thus the environment necessary to handle the next line has not been created. If that is the case, you might need to introduce a pause to allow that to happen. Can't say for certain since I don't know exactly what the bat file does. You could test that theory by stepping through the code, allowing what you think is a sufficient pause before attempting to execute the next line.

Error generally means Access cannot resolve your reference to the named object, usually due to being mis-spelled, no longer exists, can't be found or incorrect syntax.
- check that the export-import has been created in the database used to run this code and that the database is in a trusted location. It can fail due to that also.
- if the import/export deletes a table, I don't think it's possible to call from code (not sure)
- the named specification may have to be enclosed in quotes ("Import-temporary")
Or the destination for the export/import is invalid or not trusted

That's all I've got...

The problem was that DoCmd.RunSavedImportExport "Import-temporary" would start before Shell "cmd /c ""\\as65\data\Info\Ple\ConvFile.bat""", vbNormalFocus finished.

So I added a temporary pause below which fixed the issue. Thanks for everyones help.

    Shell "cmd /c ""\\as65\data\Info\Ple\ConvFile.bat""", vbNormalFocus


    Dim time1, time2


    time1 = Now
    time2 = Now + TimeValue("0:00:7")
    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop
    
    DoCmd.RunSavedImportExport "Import-temporary"
    DoCmd.RunSavedImportExport "Import-tempTitle"
    DoCmd.OpenForm "Display All"
    DoCmd.Close acForm, Me.name

[ssanfu]

And thanks for posting your solution.

Glad you were able to solve this......

[Micron]

I recommend you move that routine (the pause) to a standard module so you can use it from anywhere in the db. Probably will be useful again. there is also a timer function you might want to check out.