Reputation: 21
I have searched through some topics where a VBS file is being called via a batch file on Task Scheduler, but it doesn't run.
In my case, my VBS file does run on Task Scheduler but my logic for grabbing a record count from a shared database (.mdb) does not work correctly. Ideally, my VBS file will check how many units have been made on the present day, and output the corresponding DATE, TIME, and TOTAL units, with DATE and TIME being fixed values and TOTAL being my record count.
*It should be noted that while my VBS file does not work correctly on Task Scheduler, it does work correctly when run manually. This is something I cannot wrap my head around.
Here's an example of what my output (.csv) file should look like, running every five minutes on Task Scheduler
DATE TIME TOTAL
01/13/2017 16:30 165
01/13/2017 16:35 169
01/13/2017 16:40 173
01/13/2017 16:45 176
01/13/2017 16:50 179
but as of right now, I am only getting the output below.
DATE TIME TOTAL
01/13/2017 16:30 recordset is empty
01/13/2017 16:35 recordset is empty
01/13/2017 16:40 recordset is empty
01/13/2017 16:45 recordset is empty
01/13/2017 16:50 recordset is empty
The Task Scheduler runs this VBS code from 7AM-10PM every day. If no units have been produced yet, then TOTAL should show 'recordset is empty' until a unit has been produced. Problem is, my code has been generating files of only 'recordset is empty' for an entire day.
Here is my VBS code, separated into:
-A copy pasted code that I used to be able to run DAO on 64bit Windows 7
-Setting environmental variables
-Date logic
-Record count logic
-Output to .csv file
Option Explicit
'////////////////////////////////IGNORE - COPY PASTED TO GET DAO.DBENGINE.36 RUNNING//////////////////////////
CheckOSx86x64Args()
dim JRO, SC, DAO
Rem main code begins here
Set JRO = CreateObject("JRO.JetEngine")
Set SC = CreateObject("ScriptControl")
Set DAO = CreateObject("DAO.DBEngine.36")
Sub CheckOSx86x64Args()
Dim CmdLn, Arg
With CreateObject("WScript.Shell")
If Replace(LCase(WScript.Path), LCase(.ExpandEnvironmentStrings("%windir%\")), "") = "system32" Then
CmdLn = Replace(LCase(WScript.FullName), "system32", "syswow64")
If CreateObject("Scripting.FileSystemObject").FileExists(CmdLn) Then
CmdLn = CmdLn & " """ & WScript.ScriptFullName & """"
For Each Arg In WScript.Arguments
CmdLn = CmdLn & " """ & Arg & """"
Next
.Run CmdLn, 1, True 'added " ,1, True " in hopes of waiting until whatever process is running is done. read into bwaitonreturn
WScript.Quit
End If
End If
End With
End Sub
'////////////////////////////////////////////ENVIRONMENT/////////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'On Error Resume Next
Const ForWriting = 2
Const ForAppending = 8
Dim rst, oJet, oDB 'As DAO.DBEngine, DAO.Database, DAO.Recordset
Dim fso, GetTheBase
Dim formatDate, SQLdate, csvCurrentTime 'date format
Dim counterFSO, activeWbTest, objworkbook, objLogFile
Dim origLP, destpath
Dim Total
Const QRY_NAME = "Results" 'Table to export
csvCurrentTime = Hour(now) & ":" & Minute (now)
origLP = "\\192.168.1.240\ProductionSrvr2\SIMULATION\LongPulse.mdb"
destpath = "\\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\SM"
'///////////////////////////////////////////////DATE/////////////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'check if date provided as an argument;if provided, use that data, otherwise, determine date
If WScript.Arguments.Count > 0 Then
SQLdate = WScript.Arguments.Item(0) 'Query date, MM/DD/YYYY with 0's
formatDate = Replace(SQLdate,"/","")'File extension date
Else
SQLdate = Right("0" & Month(Now()), 2)& "/" & Right("0" & Day(Now()), 2)& "/" & Year(Now()) 'Query date
'SQLdate = "01/13/2017"
formatDate = Replace(SQLdate,"/","")'File extension date
End If
'//////////////////////////////////////////////RECORDCOUNT LOGIC/////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
Set fso = CreateObject("Scripting.FileSystemObject")
GetTheBase = "SM_" & formatDate & ".csv" 'adding csv to the file
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(origLP)
'Query database for 'distinct ID' from table 'Results' where the column 'Test_Date' contains the system date
Set rst = oDB.OpenRecordset("SELECT DISTINCT ID FROM " & QRY_NAME & " WHERE Test_Date LIKE '*" & SQLdate & "*' ")
If rst.EOF = True Then
Total = "recordset is empty"
Else
Total = rst.RecordCount
End If
oDB.Close
Set oDB = Nothing
Set oJet = Nothing
'//////////////////////////////////////OUTPUT TO .CSV FILE///////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'checks if .csv file already exists. If it doesn't, it creates file with headers. If it does, it skips the 'if' statement
Set counterFSO = CreateObject("Scripting.FileSystemObject")
If Not counterFSO.FileExists(GetTheBase) Then
Set objLogFile = counterFSO.CreateTextFile(GetTheBase, ForWriting)
objLogFile.Writeline chr(34) & _
"DATE" & chr(34) & "," & chr(34) & _
"TIME" & chr(34) & "," & chr(34) & _
"TOTAL" & chr(34) & ","
Set objLogFile = nothing 'prevents "permission denied" error.set objLogFile to nothing for the next .opentextfile action
End If
Set objLogFile = counterFSO.OpenTextFile(GetTheBase, ForAppending)
objLogFile.Writeline chr(34) & _
SQLdate & chr(34) & "," & chr(34) & _
csvCurrentTime & chr(34) & "," & chr(34) & _
Total & chr(34) & ","
Set objLogFile = nothing
I am not sure where I am going wrong in the record count logic. I start by using an SQL statement Set rst = oDB.OpenRecordset("SELECT DISTINCT ID FROM " & QRY_NAME & " WHERE Test_Date LIKE '*" & SQLdate & "*' ")
to select unique records (using ID as criteria) where the Test_Date
is similar to my SQLdate
(which is always representative of the current day).
My guess is that the problem lies in this section
If rst.EOF = True Then
Total = "recordset is empty"
Else
Total = rst.RecordCount
End If
If no units have been produced yet, my recordset should be completely empty. I use .EOF
to detect this circumstance, but as of right now the code thinks rst.EOF = True
all the time instead of recording an actual record count when units have been produced. Again, when the VBS file is run manually instead of on Task Scheduler, it is able to get a correct record count.
Here's an example of the cloud database (.mdb) file where I query ID and Test_Date for a record count
Title Comment ID Test_Date Test_Time Pmax
AU29 PL1 H160019608 01/13/2017 1:03:16 PM 351.9642
AU29 PL1 H160019917 01/13/2017 1:03:35 PM 351.9942
AU29 PL1 H160019940 01/13/2017 1:03:56 PM 356.2267
AU29 PL1 H160019940 01/13/2017 1:04:17 PM 356.2153
The batch file simply calls my .vbs file (UniqueCounter.vbs) using the following code:
set SM_DEST="\\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\SM"
set sim_count="\\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\BIN\UniqueCounter.vbs"
REM collect simulation data
pushd %SM_DEST%
%sim_count%
popd
Anyone know how I could go about troubleshooting this? Any help would be greatly appreciated.
Upvotes: 2
Views: 113
Reputation: 56026
while my VBS file does not work on Task Scheduler, it does work when run manually.
Then I don't see how and why you would change the code.
The reason for the malfunctioning is the account the scheduled task is assigned.
Assign (create) a user account to the scheduled task which you grant privileges to access the database file.
Edit:
Your where clause is wrong. It should read:
Set rst = oDB.OpenRecordset("SELECT DISTINCT ID FROM " & QRY_NAME & " WHERE Test_Date = #" & SQLdate & "#' ")
Upvotes: 1