By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Running .bat from Access

P: 5
I am trying to run a .bat file from access. On the click of a search button (btnSearch) I incorperate data entered into a text box (txtSearch) to print a script into a text file (script.txt). The .bat file which i am trying to execute uses this script, telnets to a remote computer and creates another text file with the output from its findings.
Currently the script is being written to the file without any problems, but the command prompt for the .bat file is appearing and closing straight away without executing the files leaving the output file unchanged. I have tried killing the output file before executing but it is not being recreated as it should be when the .bat is run. The .bat file runs if manually executed (double click), and the output file is update as it should. Can anyone help?

Thanks

Joe

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnSearch_Click()
  3.  
  4. Dim strList As String
  5. Dim strMSISDN As String
  6. Dim strScript1, sctScript2, sctScript3, sctScript4, sctScript5, sctScript6, sctScript7, sctScript8, sctScript9 As String
  7. Dim Retval
  8.  
  9. Me.txtSearch.SetFocus
  10. strMSISDN = Me.txtSearch.Text
  11.  
  12. strScriptLn1 = "172.21.0.55"
  13. strScriptLn2 = "WAIT " & Chr(34) & "USERCODE" & Chr(34)
  14. strScriptLn3 = "SEND " & Chr(34) & "win1\m" & Chr(34)
  15. strScriptLn4 = "WAIT " & Chr(34) & "PASSWORD" & Chr(34)
  16. strScriptLn5 = "SEND " & Chr(34) & "win123\m" & Chr(34)
  17. strScriptLn6 = "WAIT " & Chr(34) & "<" & Chr(34)
  18. strScriptLn7 = "SEND " & Chr(34) & "hgsup:msisdn=" & strMSISDN & ";\m" & Chr(34)
  19. strScriptLn8 = "WAIT " & Chr(34) & "<" & Chr(34)
  20. strScriptLn9 = "SEND " & Chr(34) & "exit;\m" & Chr(34)
  21.  
  22. strList = strScript1 & strMSISDN & strScript2
  23.  
  24. Open "\\cameron\vol1\deps\Wireless Engineers\Youtube\telnet\script.txt" For Output As #1
  25. Print #1, strScriptLn1
  26. Print #1, strScriptLn2
  27. Print #1, strScriptLn3
  28. Print #1, strScriptLn4
  29. Print #1, strScriptLn5
  30. Print #1, strScriptLn6
  31. Print #1, strScriptLn7
  32. Print #1, strScriptLn8
  33. Print #1, strScriptLn9
  34. Close #1
  35.  
  36. Retval = Shell("J:\Wireless Engineers\Youtube\telnet\test.bat", vbNormalFocus)
  37.  
  38. End Sub
  39.  
  40.  
Dec 10 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,638
@jvborg
Hello Joe, you can use the ShellExecute() API Function to execute the *.bat file directly from within Access. If you need further assistance on this, just let me know.
Dec 10 '08 #2

P: 5
@ADezii
Could you please give me an example of how to do this, because i have never used this function before and am unsure on hw to use it. I have tried a few various methods but it keeps on returning the same errors. I used the following code but it comes up with "Compile error: Expected ="

Dim path As String
path = "J:\Wireless Engineers\Youtube\telnet\test.bat"
ShellExecute(handle, "open", path, NULL, NULL, SW_SHOWNORMAL)
Dec 10 '08 #3

ADezii
Expert 5K+
P: 8,638
@jvborg
  1. Declare the API Function in a Stand Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Declare Function ShellExecute Lib "shell32.dll" _
    2. Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
    3. ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
  2. Copy and Paste this Function to a Standard Code Module (I've included extensive Error Checking within the Function):
    Expand|Select|Wrap|Line Numbers
    1. Public Function Execute_Program(ByVal strFilePath As String, _
    2.     ByVal strParms As String, ByVal strDir As String) _
    3.     As Boolean
    4.  
    5. 'run program ' <R6>
    6. Dim hwndProgram As Integer
    7. hwndProgram = ShellExecute(0, "Open", strFilePath, strParms, strDir, 3)     '3 ==> Show Maximized
    8.  
    9. 'evaluate errors (if any)
    10. Select Case (hwndProgram)
    11.   Case 0
    12.     MsgBox "Insufficent system memory or corrupt program file.", 0, "Error running " & strFilePath
    13.       Execute_Program = False
    14.         Exit Function
    15.   Case 2
    16.     MsgBox "File not found.", 0, "Error running " & strFilePath
    17.       Execute_Program = False
    18.         Exit Function
    19.   Case 3
    20.     MsgBox "Invalid path.", 0, "Error running " & strFilePath
    21.       Execute_Program = False
    22.         Exit Function
    23.   Case 5
    24.     MsgBox "Sharing or Protection Error.", 0, "Error running " & strFilePath
    25.       Execute_Program = False
    26.         Exit Function
    27.   Case 6
    28.     MsgBox "Seperate data segments are required for each task.", 0, "Error running " & strFilePath
    29.       Execute_Program = False
    30.         Exit Function
    31.   Case 8
    32.     MsgBox "Insufficient memory to run the program.", 0, "Error running " & strFilePath
    33.       Execute_Program = False
    34.         Exit Function
    35.   Case 10
    36.     MsgBox "Incorrect Windows version.", 0, "Error running " & strFilePath
    37.       Execute_Program = False
    38.         Exit Function
    39.   Case 11
    40.     MsgBox "Invalid program file.", 0, "Error running " & strFilePath
    41.       Execute_Program = False
    42.         Exit Function
    43.   Case 12
    44.     MsgBox "Program file requires a different operating system.", 0, "Error running " & strFilePath
    45.       Execute_Program = False
    46.         Exit Function
    47.   Case 13
    48.     MsgBox "Program requires MS-DOS 4.0.", 0, "Error running " & strFilePath
    49.       Execute_Program = False
    50.         Exit Function
    51.   Case 14
    52.     MsgBox "Unknown program file type.", 0, "Error running " & strFilePath
    53.       Execute_Program = False
    54.         Exit Function
    55.   Case 15
    56.     MsgBox "Windows program does not support protected memory mode.", 0, "Error running " & strFilePath
    57.       Execute_Program = False
    58.         Exit Function
    59.   Case 16
    60.     MsgBox "Invalid use of data segments when loading a second instance of a program.", 0, "Error running " & strFilePath
    61.       Execute_Program = False
    62.         Exit Function
    63.   Case 19
    64.     MsgBox "Attempt to run a compressed program file.", 0, "Error running " & strFilePath
    65.       Execute_Program = False
    66.         Exit Function
    67.   Case 20
    68.     MsgBox "Invalid dynamic link library.", 0, "Error running " & strFilePath
    69.       Execute_Program = False
    70.         Exit Function
    71.   Case 21
    72.     MsgBox "Program requires Windows 32-bit extensions.", 0, "Error running " & strFilePath
    73.       Execute_Program = False
    74.         Exit Function
    75.   Case Else
    76. End Select
    77.   'All is well if we get to this point
    78.   Execute_Program = True
    79. End Function
  3. Call Execute_Program() passing to it the Path to the Batch File as well as 2 Zero Length Strings:
    Expand|Select|Wrap|Line Numbers
    1. Dim lngRetVal As Integer
    2. Const conPATH_TO_BATCH_FILE As String = "C:\Test\Test.bat"
    3.  
    4. lngRetVal = Execute_Program(conPATH_TO_BATCH_FILE, "", "")
Dec 10 '08 #4

ADezii
Expert 5K+
P: 8,638
Hello again, jvborg. I had a little time, so I created a little demo for you on how to Run a Batch File from Access, just download the Attachment. The initial Dialog will provide the simple instructions. Good Luck.
Dec 11 '08 #5

P: 5
Hi ADezii,

Thank you for the demo and the previous post, they were brilliant and i wish i could say that they worked, but again the command prompt is opening and closing straight away and without retrieving/writing anythin to the output.txt file. Im unsure of what else i can do. The .bat file is in a network mapped file, and is completely functional when clicked and when executed from Run on the windows Start menu. The .bat file works fine with the script written from access so it cannot be a problem with the script, but it just seems like access is opening the .bat and the closing it before it has a chance to execute the commands. Would it be possible to add a pause with a set time before the Sub ends or somthing along those lines?
Dec 11 '08 #6

ADezii
Expert 5K+
P: 8,638
@jvborg
This one comes with no guarantees whatsoever, but between the final running of the Scripts, and the execution of the Batch File (Line #35 in original Post), put the system to Sleep for a specified Time Interval in seconds via:
  1. Copy and Paste this Declaration into a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  2. Sleep for a specific Interval in milliseconds (in this case 3 seconds):
    Expand|Select|Wrap|Line Numbers
    1. Sleep 3000
  3. Let me know how you make out on this one, I'm curious myself.
Dec 11 '08 #7

P: 5
I got it working!
Thanks for all of your help, but it wasnt the code which was the problem lol!
After all of that i used a simple VBA.Shell command to run the .bat file. The problem was that because the .bat was being run from a network folder, when the command prompt was appearing it was trying to run the file using a UNC path name. The command prompt does not accept the UNC path and the command prompt was reverting to my c: drive and trying to run it from there.... which it could not locate because it was located in a different folder. In the end i had to edit the .bat file to change the directory first then excute the file.
Thanks for all of your help
Dec 16 '08 #8

ADezii
Expert 5K+
P: 8,638
@jvborg
]
Glad you solved the problem.
Dec 16 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.