Solved Execute a batch file with parameters from query? (1 Viewer)

A1ex037

Registered User.
Local time
Today, 11:24
Joined
Feb 10, 2018
Messages
41
So, I am looking for a way to execute a batch file with 2 arguments. Arguments are values from a query. Query result has 10-15 rows, so it means that it should iterate through every row, pick up values from those 2 columns, pass them to batch file and execute.

In order to start a batch file, I am unable to use absolute path and shell command. I have managed to start cmd in specific directory (where bat file is located).

Code:
Private Sub btnBatch_Click()

Dim strProgName As String
Dim strArg As String
Dim strPath As String

strPath = CurrentProject.Path
strProgName = "C:\Windows\System32\cmd.exe"
strArg = "/K cd /d """

Call Shell("""" & strProgName & """""" & strArg & """""" & strPath & """", vbNormalFocus)

Any suggestions?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
13,684
Hi. Have you tried using a recordset?
 

arnelgp

error reading drive A:
Local time
Today, 18:24
Joined
May 7, 2009
Messages
11,116
what does the batch file do?
can FileSystemObject do what the batch file can do?
 

A1ex037

Registered User.
Local time
Today, 11:24
Joined
Feb 10, 2018
Messages
41
Thank you for your answers. I don't have much experience with it theDBguy, I mostly rely on examples that I can found and documentation. Batch file with 2 parameters (myfile.bat number ID), sends SMS message to our gateway (only way I could figure out how to communicate with it directly is via command line), to workers at the warehouse. Number is the worker's phone 4-digit number, and ID represent the warehouse aisle where the stock levels are to be checked for that day. I do have a query that retrieve those results, have a batch file ready, just have to figure out how to go through each row of query results, pick up values from 2 columns and parse them to the batch file.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
13,684
Thank you for your answers. I don't have much experience with it theDBguy, I mostly rely on examples that I can found and documentation. Batch file with 2 parameters (myfile.bat number ID), sends SMS message to our gateway (only way I could figure out how to communicate with it directly is via command line), to workers at the warehouse. Number is the worker's phone 4-digit number, and ID represent the warehouse aisle where the stock levels are to be checked for that day. I do have a query that retrieve those results, have a batch file ready, just have to figure out how to go through each row of query results, pick up values from 2 columns and parse them to the batch file.
See if you could start with this:
Code:
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("QueryName")

Do While Not rs.EOF
    'call your batch file here passing the arguments from the recordset
    'for example: Shell("path\to\batfile " & rs!FieldName1 & "," & rs!FieldName2)
Loop

Set rs = Nothing
Hope that helps...
 

arnelgp

error reading drive A:
Local time
Today, 18:24
Joined
May 7, 2009
Messages
11,116
it would be very difficult to use batch file with parameters.
what you can do is Re-create the batch file on each new Parameters
directly inputing the 2 parameters in the Code of the batch file.
 

A1ex037

Registered User.
Local time
Today, 11:24
Joined
Feb 10, 2018
Messages
41
I am on it tomorrow evening. We had new stock that was imported yesterday, so 2 days are we all over it.

Generally, what I have to do with all this is to send proper information to workers at the warehouse (as mentioned). Main reason is that we are working with 50% capacity since february (pandemic), so time is really of the essence. I really had a stroke of bad luck (it is my company, and just as it started to grow and develop nicely, pandemic happened). I decided to rotate workers every 2 weeks, while keeping everyone's salaries. It allows people to spend more time with their families (at once), while keeping everyone safe as possible. On the other hand, most of the tasks has fallen onto me. On one hand, I am trying to use it to increase efficiency.

I have tried executing batch file with parameters, and it is working perfectly. I can use the method that I have now (for single result in query), but it requires that i click button for each message separately. It would be much easier if I could do it with one click. I will get back with my progress tomorrow, just to clear my mind a bit.

Thank you guys.
 

A1ex037

Registered User.
Local time
Today, 11:24
Joined
Feb 10, 2018
Messages
41
Well, I finally found some time today. For a while, it looked like I might get away with the theDBguy suggestion:
Code:
Private Sub btnBatch_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryAisleInfo")

Do While Not rs.EOF
Call Shell("D:\_DB\_gateway\send.bat" & " " & rs.Fields(3)  &  rs.Fields(4))
Sleep 1000
rs.MoveNext

Loop

Set rs = Nothing

End Sub

It runs fine when I have to send one message. If I try to send two or more, I'm getting "This type of Automation is not supported in Visual Basic"

I do have "Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)", so maybe I'm somewhere wrong with sleep??
Any ideas?
 

arnelgp

error reading drive A:
Local time
Today, 18:24
Joined
May 7, 2009
Messages
11,116
LongPtr is what is suggest, a pointer.
it can be a Window handle or pointer to
structure but not a Tick count.

#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
 

A1ex037

Registered User.
Local time
Today, 11:24
Joined
Feb 10, 2018
Messages
41
Nice! That did the trick. Now everything works as it should. Still some things to be polished, but in general everything is working as expected.
 

Users who are viewing this thread

Top Bottom